Retuning a value for null records on a left outer join in an Access query

Question

I have tried all the formulas for returning a value in this situation and keep getting an error telling me I have the wrong number of arguments. Here is my query:

SELECT [ER Root].[Network Indicator], 
[ER Root].[Prov Indicator], 
[ER Root].[Pos Indicator], 
[ER].[ER Visit]
FROM [ER Root] LEFT JOIN ER ON ([ER Root].[Network Indicator] = ER.[Network Flag]) AND ([ER Root].[Pos Indicator] = ER.[POS Indicator]) AND ([ER Root].[Prov Indicator] = ER.[Category Indicator]);

I have tried:

SELECT [ER Root].[Network Indicator], 
[ER Root].[Prov Indicator], 
[ER Root].[Pos Indicator], 
ISNULL([ER].[ER Visit],0) AS "ER Visit"
FROM [ER Root] LEFT JOIN ER ON ([ER Root].[Network Indicator] = ER.[Network Flag]) AND ([ER Root].[Pos Indicator] = ER.[POS Indicator]) AND ([ER Root].[Prov Indicator] = ER.[Category Indicator

What am I doing wrong?

Answer 1

Access' ISNULL function accepts only one argument and returns a boolean value.

If you want to return 0 when [ER Visit] is Null, you can use Nz in a query run from within an Access session.

SELECT Nz([ER Visit], 0) AS ER_Visit

Note, I used ER_Visit as the alias because the db engine may complain about a "circular" reference if you attempt to re-use the field name as an alias.

If you are running the query from outside an Access session (like from classic ASP or .Net), the Nz function will not be available. You can do this instead ...

SELECT IIf([ER Visit] Is Null, 0, [ER Visit]) AS ER_Visit