I tried the following WHERE clauses to return records where the 'costper' field is empty and neither return any results.

WHERE NZ([Transactions].[CostPer])=0

WHERE [Transactions].[CostPer]=0

WHERE IsNull([Transactions].[CostPer])

Try:

WHERE [Transactions].[CostPer] is Null


Tip: If you will use the query design in design mode, it will validate your SQL syntax and also help you write it.

I just realized that the other clauses actually were working.

I am working in design mode when I can figure out how to use it. Could Access have helped me figure out to use 'is null' as the criteria?

Yes.


This mode is the easiest mode to work in. It will be worth taking a little time to learn it.

How could Access have helped me figure out to use 'is null' as the criteria?

I see now how to use the Expression builder to enter 'null' as the criteria and it gets converted to 'is null'.

Today I experimented with using Query design mode to create various joins and I was pleasantly surprised to see how easy it is.

If you have defined relationship for your tables, then when you add tables with defined relationships in the Query designer, it will automatically create the joins for you.

I'm working with a third party app that uses Access as a back end. So I have no control over the table definitions.

I use Access as a report writer for other third party apps.

If you create a front end and link to the back end database of the third party app, you can actually define relationships in your front end, even it the original developer did not create any. This will not effect the back end database.

I have a database with links to the back end tables. I've seen the relationship screen but I didn't know you can add new ones. That will certainly be useful.