Results 1 to 10 of 10
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Tricky IIf function (2000)

    In the file Copying Records and Linked Records.mdb, which you can download here, the LastNameFirst field in the qryEBookAuthors query is obtained with the expression:
    Trim(<font color=448800>IIf</font color=448800>([AuthorFirstName],[AuthorLastName] & IIf([AuthorPrefix],[AuthorPrefix] & " ","") & IIf([AuthorFirstName],", " & [AuthorFirstName],"") & IIf([AuthorMiddleName]," " & [AuthorMiddleName]),[AuthorLastName])) & IIf([AuthorSuffix]," " & [AuthorSuffix],"")
    the falsepart of an IIf function is required but I can't locate it for the first IIf(in green)in the expression above, can you?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Tricky IIf function (2000)

    In the expression as posted, the part indicated in bold italic is the Value_if_False part of the first IIf.

    Trim(IIf([AuthorFirstName],[AuthorLastName] & IIf([AuthorPrefix],[AuthorPrefix] & " ","") & IIf([AuthorFirstName],", " & [AuthorFirstName],"") & IIf([AuthorMiddleName]," " & [AuthorMiddleName]),[AuthorLastName])) & IIf([AuthorSuffix]," " & [AuthorSuffix],"")

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Tricky IIf function (2000)

    Thank you Hans,
    isn't the part that you indicated in bold italic, the falsepart of the red IIf(see below)? Otherwise what's the falsepart of the red IIf?

    Trim(IIf([AuthorFirstName],[AuthorLastName] & IIf([AuthorPrefix],[AuthorPrefix] & " ","") & IIf([AuthorFirstName],", " & [AuthorFirstName],"") & <font color=red>IIf</font color=red>([AuthorMiddleName]," " & [AuthorMiddleName]),[AuthorLastName])) & IIf([AuthorSuffix]," " & [AuthorSuffix],"")

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Tricky IIf function (2000)

    No, the red IIf has no false part, since the closing parenthesis for this IIf comes after " " & [AuthorMiddleName]. In other words, the closing parenthesis is in the wrong place.

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Tricky IIf function (2000)

    How can the qryEBookAuthors query work then since an IIf in it is missing the required falsepart?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Tricky IIf function (2000)

    Your link doesn't point to the correct file. Can you post the correct link?

  7. #7
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Tricky IIf function (2000)

    If I click the link, it opens up the correct accarch133.zip file. In any case you can go here and then download it.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Tricky IIf function (2000)

    Oops, I extracted the wrong .mdb file from the .zip file. I have now found the correct one.

    In VBA, the falsepart argument of IIf is required, but in an expression in a query or in the control source of a control on a form or report, you can omit it. If the condition is false, and if falsepart is omitted, IIf will return Null.

    I have indicated matching parentheses with the same color in the expression:

    LastNameFirst: Trim<font color=red>(</font color=red>IIf<font color=448800>(</font color=448800>[AuthorFirstName];[AuthorLastName] & IIf<font color=blue>(</font color=blue>[AuthorPrefix];[AuthorPrefix] & " ";""<font color=blue>)</font color=blue> & IIf<font color=ff8c00>(</font color=ff8c00>[AuthorFirstName];", " & [AuthorFirstName];""<font color=ff8c00>)</font color=ff8c00> & IIf<font color=magenta>(</font color=magenta>[AuthorMiddleName];" " & [AuthorMiddleName]<font color=magenta>)</font color=magenta>;[AuthorLastName]<font color=448800>)</font color=448800><font color=red>)</font color=red> & IIf([AuthorSuffix];" " & [AuthorSuffix];"")

  9. #9
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Tricky IIf function (2000)

    Very interesting. Access is more than the sum of its parts.
    I then gather concatenating a Null doesn't nullify the whole expression so the qryEBookAuthors query gives the same results if you strip it of all the falseparts with zero-length strings:

    SELECT DISTINCT tblAuthors.AuthorID, Trim(IIf([AuthorFirstName],[AuthorLastName] & IIf([AuthorPrefix],[AuthorPrefix] & " ") & IIf([AuthorFirstName],", " & [AuthorFirstName]) & IIf([AuthorMiddleName]," " & [AuthorMiddleName]),[AuthorLastName])) & IIf([AuthorSuffix]," " & [AuthorSuffix]) AS LastNameFirst, Trim([AuthorFirstName] & " " & [AuthorMiddleName]) & " " & [AuthorLastName] & IIf([AuthorSuffix]," " & [AuthorSuffix],"") AS FirstNameFirst
    FROM tblAuthors
    ORDER BY Trim(IIf([AuthorFirstName],[AuthorLastName] & IIf([AuthorPrefix],[AuthorPrefix] & " ") & IIf([AuthorFirstName],", " & [AuthorFirstName]) & IIf([AuthorMiddleName]," " & [AuthorMiddleName]),[AuthorLastName])) & IIf([AuthorSuffix]," " & [AuthorSuffix]);

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Tricky IIf function (2000)

    It depends on how you concatenate. "Giorgio" & Null results in "Giorgio", but "Giorgio" + Null results in Null.

    You can omit the falsepart if it only consists of an empty string and if you concatenate using &.

Posting Permissions

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