Domain Aggregate Functions

Domain Aggregate Functions

Getting these functions to work correctly are one of the trickier elements of Access to master for the newcomer, having several pitfalls to trap the unwary.

The general syntax is:

=DLookup("[FieldName]","[TableName]","[Criteria]")

In real terms, this translates to something like:

=DSum("[Price]","[tblOrderDetails]","[OrderID]=3")

Note that the arguments are enclosed in square brackets. Although in some cases this isn't required, I feel that it is good practice to get into, as then you are able to handle arguments with spaces in.

When working with a fixed criteria, the syntax is:

=DCount("[Price]","[tblOrderDetails]","[CustomerName]='Smith'")

Note that as this is counting the number of occurrences of a text field, the text needs to be wrapped in single quotes.

When working with a variable criteria, the syntax becomes:

=DSum("[Price]","[tblOrderDetails]","[OrderDate]=#" & DateValue & "#")

There are two points to note here. Firstly, as the criteria is a date, it needs to be wrapped in hash symbols. Secondly, note the placing of the quotation marks. When this comes to be evaluated, it is read as:

=DSum("[Price]","[tblOrderDetails]","[OrderDate]=#10/23/2000#")

Top

Last modified at 06/06/2006 15:00:52