Database Functions

In this post, we will be taking a look at Database Functions (D-Functions) and providing some examples of their usage.

Database Functions are an extremely useful feature in MS Access. They allow you to make quick queries of the database without having to write complicated SQL or open up recordsets and can help you to write quick and efficient code.

All DFunctions have the same signature expression, table[, criteria] which is similar in structure to SQL itself.

• DLookup ( field, table, [criteria] ) – Looks up a value in a table or query.
• DCount ( field, table, [criteria] ) – Counts the records in a table or query.
• DSum( field, table, [criteria] ) – Returns the sum of a set of records in a range.
• DMax ( field, table, [criteria] ) – Retrieves the largest value from a range.
• Dmin( field, table, [criteria] ) – Retrieves the smallest value from a range.
• DAvg( field, table, [criteria] ) – Returns the average set of numeric values from a range.
• DFirst ( field, table, [criteria] ) – Returns the first value from a range.
• DLast ( field, table, [criteria] ) – Returns the last value from a range.

Sub DFunctions()
    
    'These D-Functions will be using data from the teachers table
    
    Debug.Print DLookup("[LastName]", "tblTeachers", "[FirstName]='Anna'")
    'We are looking up a value in the [LastName] field of tblTeachers.
    
    Debug.Print DCount("*", "tblTeachers")
    'The asterix (*) means that we are counting all the records in the table
    
    Debug.Print DSum("[TotalPaid]", "tblTeachers")
    'Adds up all of the values from [TotalPaid]
    
    Debug.Print DMax("[RatePerHour]", "tblTeachers")
    'Returns the largest value from [RatePerHour]
    
    Debug.Print DMin("[RatePerHour]", "tblTeachers")
    'Returns the smallest value from [RatePerHour]
    
    Debug.Print DFirst("[LastName]", "tblTeachers", "[ZIPPostal]='98052'")
    'Returns the [LastName] of the first record where [ZIPPostal]='98052'
    
    Debug.Print DLast("[LastName]", "tblTeachers", "[ZIPPostal]='98052'")
    'Returns the [LastName] of the last record where [ZIPPostal]='98052'

End Sub

Gratacos Solsona 9 2980.4 13.2 11.5 Axen Wacker

Common Issues

Unfortunately, D-functions can be a little trying from time to time. It can appear that you have entered the necessary information correctly and triple checked it yet you are getting an error returned. It is worth noting that Access does what we ask it to do and if we are getting an error it is because we have inputted something incorrectly. No really.

So, here are a couple of tips for using D-Functions:

• Remember the order of arguments. D-Functions take up to 3 arguments but they must be in the correct order.DLookup(“[LastName]”, “tblTeachers”, “[FirstName]=’Anna'”)

1. 1st argument – field name
2. 2nd argument – table name
3. 3rd argument – criteria

In the example given LastName is the field, tblTeachers is the table and [FirstName]=’Anna’ is the criteria.

• Put square brackets around the field name. LastName should be [LastName].
• Don’t put square brackets around the table name. tblTeachers should NOT be [tblTeachers].
• Put square brackets around field names used in the where clause.  [FirstName]=’Anna’ good – FirstName=’Anna’ bad.
• Ensure that when using a where condition that references a text field, you put single quotations around the value.  In [FirstName]=’Anna’, because FirstName is a text field, we put Anna in single quotations. If we were using a variable instead of Anna it would look like this:
  • “[FirstName]='” & [txtFirstName] & “‘”
• If your where condition uses references a numeric field, do NOT put single quotation marks around the value:
  • “[ID]=” & [ID]

Simples!

Database Functions are definitely worth taking the time to get to master.

Related Posts

Built-In Functions Functions
Writing A Custom Function Functions