Results 1 to 8 of 8

Thread: Nested IIF?

  1. #1
    Join Date
    Apr 2004
    Location
    Nashville
    Posts
    52

    Unhappy Unanswered: Nested IIF?

    Hi everyone,
    I have to modify an make-table query that was created by someone else.

    The source table for the query is called HunterEd_Student_Registration_Table. There are 10 fields in this table.

    The make-table query has a IIF statement in it that concatenates these fields
    FirstName
    MI
    LastName
    I understand the syntax If a condition is met, true part, false part

    Here is the current statement:
    name: IIf([HunterEd_Student_Registration_Table]![MI],RTrim([HunterEd_Student_Registration_Table]![FirstName])+" "+[HunterEd_Student_Registration_Table]![MI]+" "+[HunterEd_Student_Registration_Table]![LastName],RTrim([HunterEd_Student_Registration_Table]![FirstName])+" "+[HunterEd_Student_Registration_Table]![LastName])

    This create a column called name. If a Middle Initial is in MI then FirstName, MI & LastName is together. If there is no Middle Initial in MI then just FirstName and LastName.

    My problem is a new column in the source table, Generation.

    I need to concatenate the columns if there is anything in the MI and/or Generation fields.

    Four possible combinations exist:
    FirstName, MI, LastName, Generation
    FirstName, MI, LastName
    FirstName, LastName, Generation
    FirstName, LastName

    How would I accomplish this? A nested IIF? No Clue!!!!

    Thanks for your help
    Bugme

  2. #2
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    574
    Why not just use a calculated field like:

    [FirstName] & Nz([MI],"''") & [LastName] & Nz([Generation],"''")

    Note, I removed your table name for ease of reading only. You may require it in your query.

  3. #3
    Join Date
    Apr 2004
    Location
    Nashville
    Posts
    52

    Red face

    Thanks for the suggestion.
    However, it throws the names together without spaces and put " at the end.

    For example, JohnPDoeJr"
    I need to have John P Doe Jr

    Thanks for responding so fast!

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    RTRIM([FirstName] & " " & Nz([MI],"''") & " " & [LastName] & " " & Nz([Generation],"''"))

    edit: Wait... that would do two spaces if there was no middle initial, I'll work something up here...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    RTRIM([FirstName] & iif([MI] IS NULL, " ", " " + [mi]) & " " & [LastName] & " " & Nz([Generation],""))
    Last edited by Teddy; 08-12-04 at 16:55.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  6. #6
    Join Date
    Apr 2004
    Location
    Nashville
    Posts
    52
    Thanks Teddy,
    I still get quotes at the end and in the areas where there is nothing in the field.

    JOSHUA C HOOKER ''
    BOONE ' ' WILSON ''

  7. #7
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    574
    the second argument passed to the nz function in my example had single quotes within double quotes. try it without the single quotes inside the double quotes at the end of the nz function.

  8. #8
    Join Date
    Apr 2004
    Location
    Nashville
    Posts
    52

    Wink

    It worked!!! Thanks guys.
    I used Teddys' statement and on your suggestion, jmrSudbury, I played around with the quotes. You guys really helped me out.

    name: RTrim([FirstName] & IIf([MI] Is Null,""," "+[mi]) & " " & [LastName] & " " & Nz([Generations],""))

Posting Permissions

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