UtterAccess HomeUtterAccess Wiki

Welcome Guest ( Log In | Register )

Custom Search
Edit Discussion
> ACE SQL Functions    
ACE SQL Functions

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

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:

CODE
WHERE MyColumn IS NULL;

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.

CODE
COUNT(*)

will count all rows found in a table.

CODE
COUNT([MyColumn])

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.

Edit Discussion
Custom Search
Thank you for your support!
This page has been accessed 6,215 times.  This page was last modified 01:32, 10 February 2012 by Jack Leach. Contributions by BananaRepublic  Disclaimers