Login or Sign Up to become a member!

Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.

From Wiki

The domain aggregate functions are as follows.

Commonly used:

• DAvg
• DCount
• DLookup
• DMin, DMax
• DSum

Less Commonly Used:

• DFirst, DLast
• DStDev, DStDevP
• DVar, DVarP

Using DCount as an example, the way they work is:

DCount("[SomeField]","tblMembers","[SomeField]=1")

DCount(	Name of function
"[SomeField]",	Name of field to count, it must be a string, that is, quoted (""). If there is a space in the field name, or the field name is a reserved word, you must use square brackets ([]).Otherwise, it does not matter whether you do or don't. You can also count star: "*", which will include nulls, whereas counting a particular field will not include nulls, unless you add a little extra, such as Nz.
"tblMembers"	Name of the table as a string.
"[SomeField]=1"	WHERE statement as a string. The rules shown above for square brackets apply here, too.

There is an Or for the WHERE statement, and that is, if the comparison is a variable, you put it outside the quotes. Then it gets a little more complicated, if the comparison is to a text field, you need single quotes (''), if it is a date field, you need hash signs (##) and if it is to a number, you do not need anything. So:

"[SomeField] =1"	Literally equal to 1
"[SomeField] =" & intI	Equal to a variable intI, which might contain 1
"[SomeField] ='hat'"	Literally equal to hat
"[SomeField] ='" & strA & "'"	Equal to a variable strA, which might contain hat
"[SomeField] = #01/31/06#"	Literally equal to 01/31/06. A note of caution, Access is happy enough with American dates and dates formatted year, month, day (the format I prefer), but dates formatted day, month, year will return incorrect results in variables or values.
"[SomeField] =#" & dteD & "#"	Equal to a variable dteD, which might contain 01/31/06

A control on a form can be referenced as part of the WHERE statement, of course the form must be open:

   DLookup("Surname & ', ' & Forename", "tblMembers", "SomeField=Forms!Members!Somefield") 

You do not need delimiters when referring to a control in this way. Note that the example above will return both surname and forename. The first and last arguments (what to look up, and WHERE statement) can be a number of different expressions and can include a number of fields, pretty nearly anything you would use in a similar query will work, for example:

   DAvg("[HrlyRate]*80","tblWages","Gender='F' AND Status='A' AND HireDate>=#" & dteD & "#")

Functions can also be combined:

   DLookup("code", "tblmembers", "CrDate=DMax('CrDate','tblMembers')")

Further Information

Domain Aggregate Functions: http://msdn2.microsoft.com/en-us/library/aa212172(office.11).aspx

List of reserved words in Access 2002 and in later versions of Access: http://support.microsoft.com/kb/286335