MS Access Functions
MS Access SQL is its own dialect — it sits closest to old-school VBA / Excel-style formulas. You'll meet it in small business apps and legacy reporting.
String
| Function | Does |
|---|---|
Len(s) | Length. |
UCase(s) / LCase(s) | Case. |
Left(s, n) / Right(s, n) / Mid(s, start, n) | Substring. |
InStr(s, find) | Find position. |
Replace(s, find, with) | Replace. |
Trim, LTrim, RTrim | Whitespace. |
Space(n) | N spaces. |
Numeric
Abs, Int, Round(x, n), Sgn, Mod, Rnd(), Int, Fix.
Date / time
| Function | Returns |
|---|---|
Now() | Current datetime. |
Date() / Time() | Today / current time. |
DateAdd("d", 7, x) | Add 7 days. |
DateDiff("d", a, b) | Difference in days. |
DatePart("yyyy", x) | Extract a part. |
Format(x, "yyyy-mm-dd") | Format to string. |
NULL / conditional
Nz(x, fallback), IsNull(x), IIf(cond, t, f), Switch(c1, v1, c2, v2, …), Choose(idx, v1, v2, …).
Aggregate
Count, Sum, Avg, Min, Max, First, Last, DCount, DLookup (domain aggregates that take a SQL-like string).
Type conversion
CStr, CInt, CLng, CDbl, CDate, CBool — VBA conversion functions; valid in Access SQL too.
Tip: Access SQL also supports
% and _ wildcards via ADO/OLEDB, but the Access UI shows * and ?. Same query, two grammars.Example
Example
SELECT Now(), Date(),
UCase('hi'), Left('hello', 3),
Nz(phone, 'n/a')
FROM customers;
Try it Yourself »
Exercise
MS Access NULL replacement function.
(phone, 'n/a')
Two letters; an Access classic.
Discussion
Loading…