Another great question from a student in my Access VBA Introduction course on Udemy.
“Can you help me understand how to use the DSUM() function, in Microsoft Access, with multiple criteria using VBA?”
If you are unfamiliar with the DSUM() function here is a quick overview of the function.
You can use the DSum function to calculate the sum of a set of values in a specified set of records (a domain). Use the DSumfunction in a Visual Basic for Applications (VBA) module, a macro, a query expression, or a calculated control.
For example, you can use the DSum function in a calculated field expression in a query to calculate the total sales made by a particular employee over a period of time. Or, you can use the DSum function in a calculated control to display a running sum of sales for a particular product.
You can read more on the function here: https://support.office.com/en-us/article/DSum-Function-08f8450e-3bf6-45e2-936f-386056e61a32
Basic DSUM() Syntax
DSum ( expr , domain [, criteria] )
Imagine you have an Access Table that contains PRODUCT Information.
Found within this data-set is a column that contains Units In Stock for each product. You’ve been asked to calculate the total units in stock, not just for each product individually but for all the products found within the table. In steps the DSUM() function.
The first argument in the DSUM, expr, is asking for which column you want to SUM. The second argument, domain, is asking for the table that the data is contained within. Using the function, see above, will SUM up all Units In Stock for all products.
This is great information to have. But, now you have been asked to SUM up the Units In Stock for specific products, products that match specific criteria. Also found within the Products table is a Supplier ID. Your co-workers would like to SUM up Units In Stock for specific Suppliers. The DSUM() Function contains an optional 3rd argument where you can supply criteria. This will inform the DSUM function when it should SUM the Units In Stock.
=DSUM(“[UnitsInStock]”, “Products”, “[SupplierID] = ‘Exotic Beverages'”)
The above formula will SUM up the Units In Stock for products supplied by Exotic Beverages. Once again, this is great information that can be used to create reports based on products by a specific supplier. Upon closer look, your co-workers find that this is including products that are marked as Discontinued. Now, you need to exclude the discontinued products from the DSUM formula.
=DSUM(“[UnitsInStock]”, “Products”, “[SupplierID] = ‘Exotic Beverages’ AND [Discontinued] = False”)
In the above example, the criteria argument can include multiple criteria. In this case using “AND” logic as both criteria need to true in order for the Units In Stock to be calculated.
To illustrate using this function, I have created a sample file in a simple Access Database. I created a form that contains the following:
- The form is based on the Products Table seen above
- A Combo Box where users can pick a Supplier ID
- A Text Box that will populate with the total Units In Stock, using the DSUM() Function
- A simple VBA procedure has been added to the OnChange Event of the Combo Box control
- This procedure passes the selected Supplier ID into the DSUM Function and the final calculation is then passed to the Text Box on the form
Download the working Access file here: DSUMProducts-01
- Open the form called: frmProducts
- Select a Supplier from the Combo Box, bottom of form