Last updated: August 28, 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.
Reference: Microsoft SQL Server function reference.
