Microsoft Access functions in SQL

Most functions can be used in both VBA and in a query by adding the function (and it's parameters) to the 'field' line in the query design view.

For example using the Format() and DateDiff() functions:

Select Query

If one or more table columns are used as function parameters, then the function will return a different value for each row of the result set. So the query above will return different results for every row in the table that has a different Purchase_Date.

When a function is used that has no parameters such as Now() then it will return the same value for every row returned by the query. Some VBA functions will be considered 'unsafe expressions' when used within an SQL query.

You can prefix the expression with a meaningful name or allow Access to assign a name automatically Expr1: , Expr2: ...

“Measuring programming progress by lines of code is like measuring aircraft building progress by weight” ~ Bill Gates


Format - Format a Number/Date/Time.
Now - Return the current date and time.
Sum (SQL) - Add up the values in a query result set.

© Copyright 1999-2015
Some rights reserved