In the previous post, you have learnt how DSUM() function works as well as how to specify a text criteria. As mentioned earlier, the criteria could be a text criteria or a number or a date. Depending on the data type of the criteria, there is a slight change in how you construct the criteria part of the DSUM() function in terms of the delimiters to be used as well as the placement of the criteria value.
[Note: If you are new to DSUM() function, it is recommended that you read this post first : DSUM() function – The Art of Writing a Criteria : 1]
In this post, you are going to learn more about specifying the number criteria. Let us see how…
You and your team want to know the total invoice value of all the orders placed. Its a pretty straight forward requirement but you want to use DSUM() function to achieve this.
DSUM function with Number Criteria
In order to run to get a result as required in the work scenario, you’ll be using the table “Order details” and the following field: Order Id, Unit Price, Quantity.
As a recap, the formula for DSUM function is as follows:
Also, by now you might be knowing that the criteria can be specified in two ways:
Provide a direct value:
Lets say you want to find the total invoice value of an Order ID = 40. To do so, you’ll create the criteria by directly specifying the number as follows:
Notice that when you want to specify a number criteria there are no delimiters required. You can simply specify the number directly after the = sign.
On constructing and running the query as explained above, you’ll get the following output:
And the output is:
Provide a reference to a field
You might want a similar output as shown above but you would need it for all the orders in the system. In such a scenario, you will have to construct the criteria by providing a reference to the field holding the order numbers. It will be constructed as follows:
“[Order ID]=”&[Order ID]
Note that the Order ID is the field that is holding value of data type – number.
The query would look like as follows:
On running this query, you’ll get the following result:
In this post, you have seen how to create a DSUM function when the criteria that you are passing is of a number data type.
In the next post, you’ll learn to pass a criteria of data type – Date