This article will highlight my absolute favorite family of function in MS Access - the D functions. This includes DLookUp, DSum, DMax, etc. They are widely applicable and allow data researchers to pull in a specific piece of data without the use of extraneous subqueries and complicated joins.
The D functions are meant to add a dynamic level of data reference to Access objects. In a report, the DSum function can pull in a running sum. In a fillable form, DLookUp can bring in a cost value based on categories selected elsewhere in the form. In a query, DMax can help you in assigning an incrementing auto-number value.
It its simplest form, a D function looks something like:
DSum("[Field]","[Table]","[CriteriaField] = "&[Criteria])
The difficulty with the D functions is the syntax of the 3rd argument. It's widely versatile in that it can allow as many criteria as you can fit into the expression, but correctly typing a DSum function to filter for (1) a single customer with (2) a date prior to today and (3) a specific range of transaction types can prove difficult.
The first thing you need to know is the difference between string fields and number fields in your criteria. String fields need to be quoted. To accomplish this, the third argument needs quotes around the criteria AFTER the 2 parts are concatenated together.
Number filter:
"[CriteriaField] = "&[Criteria]
String filter:
"[CriteriaField] = '"&[Criteria]&"'"
In the string filter, you have to include a single quote within the double quotes, so that the system reads the filter something like this after reading your expression:
You see:
"[Customer] = '"&[Customer]&"'"
System reads:
[Customer] = 'Amazon'
Date values are best wrapped in #'s, so your 3rd argument may look like:
You see:
"[CriteriaField] <= #" & [Criteria] & "#"
System reads:
[CriteriaField] <= #1/1/2013#
As you build more complex D functions, you may find use in applying multiple criteria to your filter. Finding the perfect syntax here may take some finesse. I find it most helpful to start with a small data set in a form, and use the Filter property to build your syntax. To get from there to here, you just have to use & to sub in your field name, and put quotes in all the right places.
The most unique application I've found for the D functions is a running sum. Running sums are simple in Excel, since you can clearly reference cells by their row, but the same isn't true in Access. The D functions give the analyst a method for adding, averaging, or counting a set of values by a specific sort order. Here's a clean way to get your expression right on the first try:
1) Identify your filter fields. Do you want to sort by a date field, or an ID field? Do you want to sum all customers, or each customer individually? I'll use:
[TransDate]
[CustID]
2) List out your criteria for each field. Customer IDs may be easy. For your sorting field (usually a date), you have to decide whether you want an all-time sum, or just the current month, calendar year, or fiscal year. I'll use:
[TransDate] >= Jan 1 of Year([TransDate])
*[TransDate] <= [TransDate]
[CustID] = [CustID]
*(This is where the running sum comes into play. The DSum function calculates each row based on criteria found in the current row, so that the function will some applicable values UP TO the current value.)
3) Turn each of your criteria values in to individual D function filters. You can write and test each filter individually to keep from hunting down errors later. Mine turn into:
"[TransDate] >= #1/1/"&Year([TransDate])&"#"
"[TransDate] <= #"&[TransDate]&"#"
"[CustID] = "&[CustID]
4) Throw them together. Use the format (criteria 1) And (criteria 2) And (criteria 3). Note, this does not need the outer parentheses sometimes used in form filters. My final criteria argument is:
"([TransDate] >= #1/1/"&Year([TransDate])&"#) And ([TransDate] <= #"&[TransDate]&"#) And ([CustID] = "&[CustID]&")"
5) Choose your D function, and plug in your first 2 arguments. The first argument is the field name, the second is the table or query name where the function is looking for both the calculated field and the filter criteria. Note, you can use a query as the source of your data, but keep in mind the D function may requery the source for each line it calculates, so it should only come from a simple query. My final DSum function looks like:
DSum("[TotCost]","[OrdersTbl]","([TransDate] >= #1/1/"&Year([TransDate])&"#) And
([TransDate] <= #"&[TransDate]&"#) And ([CustID] =
"&[CustID]&")")
You may also hear about the custom T functions, which are an attempt to improve the speed of D functions. Try at your own risk, but this is a custom code that you will have to input into each database you manage. I'll discuss custom VBA codes another time, and once I get some experience with the T functions, I'll put up a review of them. Until then, here is the list of D functions available within Access:
DLookUp
DSum
DMax
DMin
DCount
DAverage
Happy querying,
Tyler
testing
