Nz or IIf(is null) to force a certain value?

[crazy_loud]

I have a query in Access 2000 that I can't get to force a certain value in empty rows for a particular field column - maybe someone here can see why?

The SQL for the query is as follows (give or take some)...:

TRANSFORM Count([Testing DB].[DB_ID]) AS [CountOfDB_ID]
SELECT [Testing DB].State, [Location].[North], Count([Testing DB].[DB_ID]) AS [Total Of DB_ID]
FROM [Testing DB] LEFT JOIN [Location] ON [Testing DB].[Address] = [Location].Address
WHERE ((([Location].[Size])=Nz([Size],"Big")) AND (([Testing DB].[Window Length]) Is Not Null)
GROUP BY [Testing DB].State, [Location].[Size]
ORDER BY [Testing DB].State, [Location].[Size]
PIVOT Format([Date],"mmm-yyyy");

I want my crosstab to end up so that any records in the "Size" column that are not matched between the two tables, and therefore come up empty for those rows in the query results, are forced to change from empty to "Big" - so that they can be added into the count of "Big" hits that the query ends up with in the "Size" column. But all my "nz" addition did was remove the columns with blank values from the crosstab - no addition to the "Big" counts.

Am I perhaps going about this incorrectly? Maybe I should have used some form of iif(is null) instead. Any help is, as always, much appreciated.

 

[WayneRyan]

Crazy,

I can't help you with the query part, but you definitely have unbalanced parens.

Wayne

 

[crazy_loud]

Thanks for the response Wayne,

Clearly I am not thinking out this query correctly. I'm going to re-design it after I've pondered for awhile about how one would make this concept work...

 

[Moniker]

Note the subtle change in the usage of Nz:

TRANSFORM 
    Count([Testing DB].DB_ID) AS CountOfDB_ID
SELECT 
    [Testing DB].State
    ,Location.North
    ,Count([Testing DB].DB_ID) AS [Total Of DB_ID]
FROM 
    [Testing DB] 
LEFT JOIN 
    Location ON [Testing DB].Address = Location.Address
WHERE 
    Location.Size = "Big" 
    OR Nz(Location.Size,"Big") = "Big"
    AND [Testing DB].[Window Length] Is Not Null
GROUP BY 
    [Testing DB].State
    ,[Location].[Size]
ORDER BY 
    [Testing DB].State
    ,[Location].[Size]
PIVOT 
    Format([Date],"mmm-yyyy");

You have to transform on the field itself, not the result of the field. I believe that will correct the issue you were having (but I could be wrong ).

Also note that it's helpful to do a little bit of formatting on the SQL, especially when looking for help. You don't have to use the format I use, but something more than a copy/paste out of SQL view in Access is far clearer and easier to read, and I would imagine it may garner more responses.

 

[crazy_loud]

Thanks Moniker! That change to Nz did the trick.

I will be certain to re-format the postings I make in the future to be more readable.