Retuning a value for null records on a left outer join in an Access query
left-join, ms-access
Solution
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
Problem
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?