Please help with NZ being ignored. (1 Viewer)

[wrightyrx7]

Hi all,

I have the below in a query to select my required data.

SELECT 
table_MainData.*, 
Nz([Structure lvl5],[Structure lvl4])) AS Location
FROM table_MainData;

When i check the data in Datasheet View it looks fine, there is NO blanks in the Location field. But if i export to excel the NZ values are gone..

The important part though is if i create another query from the above query with GROUP BY

SELECT 
MI_REPORTS.Location, 
Count(MI_REPORTS.ID) AS CountOfID
FROM MI_REPORTS
GROUP BY MI_REPORTS.Location;

It treats it as though its not running the NZ in the 1st query. So if Structure lvl5 was blank in table_MainData it is blank in the results of the second query.

Its like i cannot group by on the NZ value.

Please help. I cannot move forward with my work until i figure this out.

Thanks
Chris

 

[arnelgp]

don't use MI_REPORTS query in your Total Query.
you have to mimic (re-create) what you have done (including the Expression)
on MI_REPORTS to your Total Query.

 

[Frothingslosh]

Actually, as shown in the attached database, what the OP is trying to do should work just fine - I use that methodology myself when necessary.

The real issue is trying to find WHY his version isn't working. And there, I'm stumped. It works precisely as intended when I tried it.

 

[plog]

When i check the data in Datasheet View it looks fine, there is NO blanks in the Location field

'Blank' is not a technical term. NULL is and so is empty string. They are completely two different things, but appear the same to a human eye.

NULL is the absence of data. An empty string is a 0 length string which is not technically NULL. So when you NZ() an empty string it returns the empty string because it is not null. Again, both appear the same to the human eye.

One way to test it is with the Len() function. Empty strings will have 0 length, NULL will return no results (blank) for the length.

 

[Frothingslosh]

Plog, my first response was pretty much exactly what you posted.

Then I noticed this and deleted it:

When i check the data in Datasheet View it looks fine, there is NO blanks in the Location field.

Wright, can you please confirm that you actually see values in Location in every record when you run the first query? That *NONE* of them appear blank?

 

[wrightyrx7]

Wright, can you please confirm that you actually see values in Location in every record when you run the first query? That *NONE* of them appear blank?

Yes i can confirm this is correct. The first query is fine, its when i create the second query when things start going weird.

EDIT:

Appears the result were not all there in Location.

 

[wrightyrx7]

Edited the above because i found that some Locations was not there.

After reading all the above about NULL's and 0 Length String's i tried the following code and it now seems to be working.

SELECT 
table_MainData.*, 
IIf(IsNull([structure lvl5]) Or [structure lvl5]="",[structure lvl4]),[structure lvl5]) AS Location
FROM table_MainData;

Thank you all for taking the time to replay.

And sorry if i annoyed some people using the term BLANK.

 

[plog]

You didn't annoy--It's something that is non-intuitive and technical. Wanted to illustrate that there was a difference.

 

[Frothingslosh]

You didn't annoy me at all, and I'm probably the most irritable one on the thread.

One improvement you might try:

SELECT
    table_MainData.*
    , IIf(Nz([Structure lvl5],"") = "", [Structure lvl4], [Structure lvl5]) AS Location
FROM
    table_MainData;

It basically just replaces the whole 'is null...or' thing with the NZ you wanted to begin with.