---

SQL standard provides a list of functions which Access implements within the ACE. It is important to not confuse ACE SQL functions with VBA functions which also can be used in a query. Within Access, there is no practical difference between using a native ACE SQL function or VBA function, but when automating ACE outside of Access, it is possible that VBA functions will not function correctly. More importantly, there are functions that may be provided by ACE SQL and should be preferred over VBA's equivalent functions in context of queries.

This is only a partial list. Whenever you find any SQL function and you are certain that this is not VBA functions, please feel free to add it to here.

Contents 1 Non-aggregating functions 1.1 IS NULL 2 Aggregating functions 2.1 Min() 2.2 Max() 2.3 Count() 2.4 Sum() 2.5 First() 2.6 Last()

Non-aggregating functions

IS NULL

Technically not a function, but it bears noting that this should be used in lieu of VBA's IsNull() function.

Thus this query:

will return all rows where the column is null.

Aggregating functions

Whenever those aggregating functions are used in a query, it is usual that a corresponding GROUP BY will be also necessary to define how aggregating should be done across a set of rows. GROUP BY is not mandatory but omitting this means the aggregating will apply to the whole table which is not always the question asked.

Min()

Return the minimum value of a given column or expression.

Max()

Return the maximum value of a given column or expression.

Count()

Return the count for a given columns or expression.

Be aware of how it handles nulls.

will count all rows found in a table.

will count all rows found that does not have a null value for MyColumn.

Sum()

Totals all values for a specified column.

First()

Returns first entered record. This is rarely used; Min() is usually appropriate for obtaining first record of a table with an appropriate criteria.

Last()

Returns last entered record. This is rarely used; Max() is usually appropriate for obtaining last record of a table with an appropriate criteria.