Last updated: August 29, 2026.
Many Access functions have direct SQL Server counterparts, but date arithmetic, Null handling, wildcard syntax, and type conversion often need deliberate translation. Test results with Nulls, boundary dates, and locale-sensitive text.
Common mappings
| Access | SQL Server | Important difference |
|---|---|---|
Nz(value, 0) | COALESCE(value, 0) | COALESCE is standard SQL and can accept several arguments. |
IIf(test,a,b) | CASE WHEN test THEN a ELSE b END | CASE is the usual server-side conditional expression. |
Date() | CAST(GETDATE() AS date) | GETDATE includes time. |
DateAdd("d",7,d) | DATEADD(day,7,d) | The interval is an identifier, not a quoted Access code. |
DateDiff("d",a,b) | DATEDIFF(day,a,b) | Both count interval boundaries. |
Len(text) | LEN(text) | SQL Server LEN ignores trailing spaces. |
Translate expressions in context
This Access calculated field becomes a CASE expression in SQL Server.
-- Access
SELECT IIf(IsNull([ShippedDate]), "Pending", "Shipped") AS Status
FROM Orders;
-- SQL Server
SELECT CASE
WHEN ShippedDate IS NULL THEN 'Pending'
ELSE 'Shipped'
END AS Status
FROM dbo.Orders;Review wildcard and identifier rules
Access may use asterisks with LIKE, while SQL Server uses percent signs. Access brackets can quote names, but renaming awkward identifiers during migration is often cleaner. Move filtering and aggregation to server-side SQL when practical, then compare row counts and representative results.
Planning to move an Access application online?
Function translation is only one part of moving tables, forms, reports, and security. Review the Access replacement options.
Translate behavior, not only names
A function mapping is the start of migration, not the full conversion. Check return types, Null behavior, date boundaries, wildcard syntax, and whether calculations should execute on the server.
Run representative Access and SQL Server queries against the same test data and compare row counts and edge cases. Pay special attention to trailing spaces, locale-sensitive formats, and dates near month or year boundaries.
- Replace IIf with CASE.
- Review Nz and COALESCE types.
- Keep formatting out of filtering expressions.
Use realistic sample data that includes Nulls, ties, and boundary dates. Save a copy before converting a SELECT query into an action query, and compare row counts before relying on the result.
Continue with Null handling in Access and date range queries.
Reference: Microsoft SQL Server function reference.
