IIF failure

Topic

New Reply

Here is the query:
ReportName:Iif([Nickname]=””,[FirstName],[Nickname])

When that runs, most records come back correctly, but some records with no nickname come back with a blank ReportName field, and there is a name in the FirstName. But I should be seeing the FirstName value in ReportName.

I thought perhaps there was some “invisible” text in Nickname, so I tried the following and got similar results:
ReportName:Iif(Len([Nickname])=0,[FirstName],[Nickname]) (means Nickname has a length, since it seems to have returned the Nickname)
ReportName:Iif(IsNull([Nickname]),[FirstName],[Nickname]) (means Nickname is NOT Null since it seems to have returned the Nickname)
It appears that there is an invisible value in the Nickname field, but If I typed anything into the nickname field, the ReportName came back OK. If I then delete the value in the Nickname field, once again, the ReportName comes back blank.

This finally fixed it for me:
ReportName:Iif([Nickname]””,[Nickname],FirstName])
If there is no value in Nickname, the FirstName shows up in ReportName.

So, what could be in the “empty” nickname fields to cause this? Is there some kind of Mass Update I could do to “fix” the field.
Right now my revised code is working, but I am curious about the tech issue in the background. The really strange thing is that most of the records do work correctly showng the FirstName, but about 10% with the same condition (Has FirstName, no Nickname) show nothing.

Perhaps this means that the database needs to be repaired/reindexed?

Reply | Quote

Replies

ReportName:Iif([Nickname]=””,[FirstName],[Nickname])

This does not test for a Nickname that is genuinely empty. Instead it tests for a Nickname that contains a zero length string “”)
To test for an empty nickname use:

ReportName:Iif(isnull([Nickname]),[FirstName],[Nickname])

It seems that many of your nicknames do in fact contain zero length strings. Did you import the data from Excel perhaps?

Create a query and put “” on the criteria line against Nickname and see how many records are returned. If records are returned, convert the query into an Update query and put Null on the Update To line.

Reply | Quote

Thanks John.

That solved it for me.

When I searched for “” I got about 90% of the records back with “blank” fields. I ran a Mass Update to make them Null, then changed the query to check for ISNULL, and it worked perfectly.

This was the final query after cleaning up the existing records, identical to your suggestion:
ReportName: IIf(IsNull([Nickname]),[FirstName],[Nickname])

It is possible that the data was imported. I was asked to support this database today but never saw it before. It was created by “somebody who used to work here a few years ago.” They don’t even remember that person’s name. I suspect that I will be running into quite a few issues that are new for me with this package. I have not worked with Access for a few years, so I will be trying to get back up to speed again.

Do I need to change the Tables to not allow zero length fields, even though this field is not mandatory?

I really appreciate your quick response with the correct diagnosis and solution. Thanks again….

Reply | Quote

Do I need to change the Tables to not allow zero length fields, even though this field is not mandatory?

This used to be the default in Access (zero length strings not allowed”), but it is not anymore. I usually leave them as allowed.

You can’t type a zero length string into a field, so (I think) the only way they get there is

[*]via code ( me.txtnickname=””)[*]or via an import from a text file or Excel

So if you don’t permit them, those actions will produce errors that may be hard to understand when they occur.

You could change the test to the following to allow for both possibilities.

ReportName:Iif(isNull([Nickname]) or ([nickname]=””),[FirstName],[Nickname])

Reply | Quote

This used to be the default in Access (zero length strings not allowed”), but it is not anymore. I usually leave them as allowed.

You can’t type a zero length string into a field, so (I think) the only way they get there is

[*]via code ( me.txtnickname=””)[*]or via an import from a text file or Excel

So if you don’t permit them, those actions will produce errors that may be hard to understand when they occur.

You could change the test to the following to allow for both possibilities.

ReportName:Iif(isNull([Nickname]) or ([nickname]=””),[FirstName],[Nickname])

Since that’s the case, and I have cleaned up the fields, I will leave it alone. I suspect this is probably left over from Excel imports, so I will have enough work going through other tables and purging all the “empty” fields. Easier to do it now while its fresh iin my mind vs. waiting for another future glitch.

Thanks again for your help John.

Reply | Quote

an even simpler solution is to replace

Iif(isNull([Nickname]) or ([nickname]=””) …

with

iif([NickName] & “” = “” …

when you concatenate [Nickname] to an empty string the result is an empty string if [Nickname] is either an empty string or a null.
the following are both true

Null & “” = “”
“” & “” = “”

same result, just shorter

Reply | Quote

Yet another alternative

Code:

IIF(NZ([nickname])=""

The NZ function converts a Null to a zero length string (if you don’t specify an alternative).

Reply | Quote