CODEWITHSUNDEEP
ms-accessleft-join
Judy Boggs Jankowski
Judy Boggs Jankowski

Reputation: 51

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

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?

Upvotes: 5

Views: 5583

Answers (2)

HansUp
HansUp

Reputation: 97101

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

Upvotes: 6

TildalWave
TildalWave

Reputation: 1667

You have a few errors in your SQL code:

  • ISNULL isn't a two argument function, it merely evaluates if a (variant) value is NULL and returns a Boolean value (True or False). The function you're searching for is NZ:

You can use the Nz function to return zero, a zero-length string (" "), or another specified value when a Variant is Null. For example, you can use this function to convert a Null value to another value and prevent it from propagating through an expression.

The second problem in your query is:

  • You're enclosing a human readable filed name in double quotation marks ". That's not permissible and you should enclose it within square brackets [].

Your query would thus look like this:

SELECT [ER Root].[Network Indicator], 
[ER Root].[Prov Indicator], 
[ER Root].[Pos Indicator], 
Nz([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]);

In case you're not running this query directly within the Access application itself, but rather in some external application that uses Access database through ADO, JET, DAO, ODBC, or any other external Access driver, you should also note that NZ isn't supported through these drivers. IIF however is and you could select your ER Visit as:

IIF([ER].[ER Visit] Is Null, 0, [ER].[ER Visit]) AS [ER Visit]

If however you're trying directly in Access then you shouldn't have problems using the NZ function.

Cheers!

Upvotes: 3

Related Questions