NZ does not return the specified text

[Victor70]

Hello everyone,

My query returns a numeric designator for the area that was determined and blank (null???) for those, that have not been identified.

I cannot figure out why this does not work

Region1: nz([Region],"Not Defined")

but this works

Region2: IIf([Region]="","Not Defined",[Region])

Is not this the whole purpose of the NZ function to replace blanks with something?

Thanks in advance for your thoughts.

 

[jdraw]

Hello everyone,

My query returns a numeric designator for the area that was determined and blank (null???) for those, that have not been identified.

I cannot figure out why this does not work

Region1: nz([Region],"Not Defined")

but this works

Region2: IIf([Region]="","Not Defined",[Region])

Is not this the whole purpose of the NZ function to replace blanks with something?

Thanks in advance for your thoughts.

No. It is meant to substitute a specified value if the field/variable isNull.
see http://allenbrowne.com/vba-NothingEmpty.html

 

[Victor70]

Thanks a lot, jdraw!

So, in this case "" is empty, but not isNull?

The thing is that I have two fields (district and region). On the surface the query returns values and "blanks" (for not identified areas) for both of them. The data are coming from the table with all fields as text. The NZ works fine on one and does not on another.
???????

 

[jdraw]

"" is a zero length string
" " is a space or blank

It could be that your query is set up to ignore nulls.
see also
http://allenbrowne.com/casu-12.html

You could try a few separate queries to see exactly what is in District and Region.
something like this-
Select Region, Count(*) from yourTable
Group By Region

Or you can create a small database in mdb format(Access 2003) with the table with the District and Region and post it. I'll take a look, but I have Acc2003 and can not use an accdb file.

 

[Victor70]

Please see attached the sample database.
The query has two NZ functions - one works, another - does not.

I believe this has to do with something very simple, but I cannot figure out what it is.

Thanks.

 

[jdraw]

I created a couple of queries based on your data.

Please see the queries in the attached mdb in the zip file.


There are empty (zero length string )values for District and
Null values in Neighborhood.

 

[Victor70]

Thank you.
This makes sense on the front-end of this issue. notNULL & empty vs. NULL & Not Empty will produce different results.
However, if I go to the back-end of it, to the table, I cannot see why I would have this difference. The blank cells in the table for both fields look very much the same for me.

 

[jdraw]

And that's the issue. They are not the same one is Null, the other is zero length string.

As for your table, you would normally have a primary key; and you would normally edit the data so as to prevent unwanted "illegal/invalid" values.


If these values were entered by people via a form, you could use something like

if (IsNull(submittedValue) or sumittedValue = "") then ask for a proper value.

I don't know how they got there, but we have shown there are Nulls in neighborhood and zero length strings in District.

 

[Victor70]

Thank you for your input!

 

[jdraw]

You are welcome. Good luck with your project.