MS Access Functions
Access queries can use expressions and functions from the Access environment. They are useful when maintaining an Access database, but they are not a portable replacement for SQL functions in a web application's database server.
Understand the idea
An Access expression can combine labels, handle missing values with Nz, and calculate dates with DateAdd. The ampersand is a concatenation operator in this context, not a function called Concat. Access date literals use hash delimiters, and wildcard behaviour can depend on its ANSI query mode. Keep these details separate from the SQLite examples used in the main course.
Where to try it
Use MS Access for this example. It is shown for reading and local practice; the SQLite browser editor does not run this dialect or supply application-driver parameters. Follow the setup and explanation below in a disposable local environment.
SQLite sample data · MySQL / MariaDB sample data · See the sample tables
A worked example
SELECT 'Order ' & 101 AS label,
DateAdd('d', 7, #2026-01-05#) AS follow_up;
Read the result
In an Access query environment, the label is Order 101 and the follow-up date is 2026-01-12, displayed using local formatting. This expression is not a SQLite or MySQL statement.
Try one change
Use a disposable Access query to compare a NULL field with Nz(field, 'Not recorded'). Keep the original stored missing value separate from a report label.
Access expression directory
| Function or expression | Use and caution |
|---|---|
Asc, Chr | Convert a leading character to a code or a code to a character; encoding assumptions matter. |
Text & text | Concatenate with the ampersand operator; this is not a function named Concat. |
InStr, InStrRev | Find text from the start or end; specify comparison rules where needed. |
LCase, UCase | Change letter case; this is not a substitute for a deliberate key normalisation policy. |
Left, Right, Mid | Read part of a string by position; check short and missing values. |
Len | Measure a value's length using Access expression rules. |
LTrim, RTrim, Trim | Remove outer spaces while preserving the original stored value unless you explicitly update it. |
Replace, StrReverse, Space, String, StrComp | Replace text, reverse it, build padding, repeat a character, or compare strings. |
Format | Produce display text; retain underlying date or number types for sorting and calculation. |
Abs, Sgn, Int, Fix, Round | Magnitude, sign, integer conversion, and rounding; Int and Fix differ for negative numbers. |
Sqr, Exp, Log, Sin, Cos, Tan, Atn | Numeric calculations with suitable domains and angle units. |
Rnd, Randomize | Pseudo-random generation and seeding for non-security uses in the Access/VBA environment. |
Date, Time, Now | Read the current local date or time; document time-zone assumptions. |
DateAdd, DateDiff, DatePart | Shift dates, compare intervals, or extract a chosen calendar component. |
DateSerial, TimeSerial, DateValue, TimeValue | Construct or parse temporal values; locale and input interpretation matter. |
Day, Month, Year, Hour, Minute, Second, Weekday | Extract components; choose the first-day-of-week convention explicitly. |
MonthName, WeekdayName | Produce display names according to requested options and locale. |
IsDate | Check whether an expression can be interpreted as a date in that environment. |
CBool, CByte, CCur, CDate, CDbl, CDec, CInt, CLng, CSng, CStr, CVar | Convert to a chosen value type; conversion can fail or round, so validate inputs. |
Nz, IsNull | Supply a fallback for NULL or test whether a value is NULL; an empty string is a separate case. |
IIf, Switch, Choose | Choose a result from conditions or a position; do not assume unchosen expressions cannot be evaluated. |
IsNumeric | Test numeric interpretation; still validate the exact range and type needed by the application. |
Count, Sum, Avg, Min, Max | Aggregate query rows at the intended grain. |
CurDir | Read the current directory in the Access/VBA environment; not a portable server SQL function. |
For engine-specific options, see the Microsoft Access function documentation.