How do I check if a variable is null and assign it a value if it is, in an Access query?

I’m not a big Access user, but I need to work with it from time to time. Now I have run into a, for me, completely new task with Access. I can solve this type of problem with SQL in MS SQL Server. But the SQL syntax in Access doesn’t recognise my SQL code at all so I guess it’s quite different from “regular” SQL.

Anyway, I have a table that needs to be sorted over some attributes. For this question we can take two and call them A1 and A2. A1 doesn’t have any null values, A2 does. The sort order is on A2:

• If A2 has a value, sort by it.
• If A2 is null, then sort by the value from A1.

I have tried so many different ways to solve this and all of them has failed. It’s absolutely clear that my knowledge in Access is limited so I guess that I’m approaching this in a faulty way.

Answer

Use NZ():

ORDER BY NZ(A2, A1)

NZ returns the second parameter if the first is null, otherwise it returns the first parameter.