Nz() function not working in Access 2010 on Text field

[cliff.clayman]

I have a text field that has NULL values and I am trying to make the query return N/A when the value is NULL. Here is the expression I am using in the query:

AttName: Nz([NameField],"N/A")



It is not returning N/A. All I see is a blank and I am trying to compare it to another field where the value is N/A and the query is failing. What could be causing this? I also tried IIf(IsNull([NameField],"N/A",[NameField]) and that fails too which leads me to believe that it is something with the field type itself.

[CJ_London]

chances are you field is not null but a zero length string try

AttName: iif(Nz([NameField],"")="","N/A",[NameField])

[cliff.clayman]

Yes, that worked. Is there something I can do to fix the field?

[CJ_London]

in the table design for that field, change the Allow Zero Length property to No and ensure nothing is specified in the default value- you'll probably need to run a query to remove any zero length strings before applying this change.

Note that having null strings can introduce other issues when manipulating data so you may want to consider using the format property - see this link

https://support.office.com/en-us/art...rs=en-US&ad=US

put

@;"N/A"

in the format property in either your table or query and see if this works for you

I use this on forms quite a bit

@;[Red]"This field must be completed"

as a visual reminder to users - you still need to have suitable routines to ensure users cannot ignore it however - but they have been warned!

[cliff.clayman]

The data is coming in from an Excel file and I tried to change Allow Zero Length to No, but that didn't fix it. How can I run a query to remove the zero length strings?

[CJ_London]

update mytable set myfield=null

If your table is linked to excel then this won't work

[cliff.clayman]

It's not linked. I import the data from the file. Isn't that query missing something? That will set all values to NULL, correct? I think it needs to look like this:

UPDATE mytable SET myfield= "N/A"
WHERE (((myfield)=" "));

[CJ_London]

whatever rocks your boat!

[DaveT99]

whatever rocks your boat!

I have just spent an hour trying to solve the same issue, your solution worked great

Dave