Core Code
Core Code Modern Dev Handbook

MS Access Functions

MS Access function quick reference for queries: text, date/time, numeric, conditional logic, and NULL handling. Short explanations with Access-style examples.
On this page

Notes

  • Access SQL and functions differ from MySQL/SQL Server.
  • Examples below use typical Access query syntax.

Text functions

  • Left(text, n), Right(text, n), Mid(text, start, n)
  • Len(text)
  • InStr([start, ]text, find) – position
  • Replace(text, find, repl)
  • LCase(text), UCase(text)
  • Trim(text), LTrim(text), RTrim(text)
SELECT Left([FullName], 3) AS Prefix
FROM People;

Date and time

  • Date() – today (date only)
  • Now() – current date/time
  • DateAdd(interval, n, date)
  • DateDiff(interval, a, b)
  • DatePart(interval, date)
SELECT DateAdd('d', 7, Date()) AS NextWeek;

Numeric

  • Abs(n), Int(n), Fix(n), Round(n, dec)
  • Rnd() – random number

NULL handling and conditionals

  • Nz(value, [alt]) – replace Null with alt (or 0/empty)
  • IIf(cond, truePart, falsePart)
  • Switch(cond1, val1, cond2, val2, ...)
SELECT Nz([Nickname], [FirstName]) AS DisplayName
FROM People;

Domain aggregate functions (very Access-specific)

  • DCount(expr, domain, [criteria])
  • DSum(expr, domain, [criteria])
  • DLookup(expr, domain, [criteria])
SELECT DCount('*', 'Orders', 'Status="paid"' ) AS PaidOrders;

Quick reminders

  • Access uses * and ? as wildcards in some contexts; in queries, LIKE patterns commonly use *.
  • Nz is the go-to for Null-safe expressions.
  • DLookup/DCount are convenient but can be slow on large tables.
SQL Quick Ref →