Results 1 to 7 of 7

Thread: IIF failure

  1. #1
    Star Lounger
    Join Date
    May 2006
    Posts
    51
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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?

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Quote Originally Posted by obfusc88 View Post
    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.
    Regards
    John



  3. #3
    Star Lounger
    Join Date
    May 2006
    Posts
    51
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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....

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Quote Originally Posted by obfusc88 View Post
    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])
    Regards
    John



  5. #5
    Star Lounger
    Join Date
    May 2006
    Posts
    51
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by John Hutchison View Post
    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.

  6. #6
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Brisbane, Queensland, Australia
    Posts
    387
    Thanks
    0
    Thanked 1 Time in 1 Post
    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

  7. #7
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    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).
    Regards
    John



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •