Converting T-SQL for MS Access SQL Queries

I’m moving to pastures new and will be leaving behind a well-loved SQL Server database, complete with a rich array of patient level data. At my new job I will likely have to make do with an Access database and so I’ve been doing a bit of preparation, building a quick cheat sheet so I can continue to build SQL queries in the new environment.

So far I’ve been tripped up by the simplest of select queries due to the slight differences in Access SQL (JET SQL) and T-SQL (used on Microsoft SQL Server).

Eg.

T-SQL Select where Value contains…..

1
2
SELECT * FROM dbo.episodes
WHERE spell_hrg like 'PB%'

in T-SQL the multi character wildcard is % while in Access it is *. Strings are also identified in access with the double speechmark “string” rather than single quotes ‘string’

Access SQL Select where Value contains…..

1
2
SELECT * FROM dbo.episodes
WHERE spell_hrg like “PB*”

String Functions

Match multiple characters
T-SQL:

1
WHERE Value like ‘PB%’

Access SQL:

1
WHERE Value like “PB*”

Match single characters
T-SQL:

1
WHERE Value like ‘PB0_Z’

Access SQL:

1
WHERE Value like “PB0?Z”

Trimming blanks
T-SQL:

1
RTRIM(LTRIM(Value)

Access SQL:

1
TRIM(Value)

NULLS

Checking for NULLs
T-SQL:

1
WHERE Value IS NULL

Access SQL:

1
WHERE Value IS NULL -- or -- WHERE ISNULL(Value) (note the difference from T-SQL's ISNULL)

Converting NULL values
T-SQL:

1
COALESCE(Value, ValueToReturnIfNull) -- or -- ISNULL(Value, ValueToReturnIfNull)

Access SQL:

1
NZ(Value, ValueToReturnIfNull)

Conditional Expressions

T-SQL:

1
CASE WHEN Condition THEN ReturnIfTrue ELSE ReturnIfFalse END

Access SQL:

1
IIF(Condition, ReturnIfTrue, ReturnIfValue)

Link to a far more comprehensive Cheat Sheet

  • Hugh

    Good luck in new position Dr Moxie.