Results 1 to 11 of 11
  1. #1
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    IIF statement in query (with concat) (2000)

    Okay, here's my formula:

    NAME: [rank] & " " & [firstname] & " <span style="background-color: #FFFF00; color: #000000; font-weight: bold">" & [middleinitial] & ".</span hi> " & [lastname] & " " & Right(Format([ssn],"@@@ @@ @@@@"),11) & "/" & [mos]

    I want to omit the highlighted section if [middleinitial] is null - but I can't seem to get it right. I've tried numerous variations of IIf(IsNull([MiddleInitial].....etc, but Can't seem to get it right... <img src=/S/help.gif border=0 alt=help width=23 height=15>?
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  2. #2
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: IIF statement in query (with concat) (2000)

    <img src=/S/nope.gif border=0 alt=nope width=15 height=15> (as in doesn't work), but I hadn't tried that, either. <img src=/S/hmmn.gif border=0 alt=hmmn width=15 height=15>
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  3. #3
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Schenectady, New York, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: IIF statement in query (with concat) (2000)

    Could lastname be = "" instead of Null?
    Don
    <img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18> <img src=/S/flags/NewYork.gif border=0 alt=NewYork width=30 height=18> "Life on Earth is expensive, but at least it includes a free trip around the Sun."

  4. #4
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Schenectady, New York, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: IIF statement in query (with concat) (2000)

    Jeremy,

    Did you try
    NAME: [rank] & " " & [firstname] & " " & iif(isnull([middleinitial]), [lastname] & " " & Right(Format([ssn],"@@@ @@ @@@@"),11) & "/" & [mos], ". " & [lastname] & " " & Right(Format([ssn],"@@@ @@ @@@@"),11) & "/" & [mos])

    oops I had and extra & in there and a missing ).
    Don
    <img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18> <img src=/S/flags/NewYork.gif border=0 alt=NewYork width=30 height=18> "Life on Earth is expensive, but at least it includes a free trip around the Sun."

  5. #5
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Schenectady, New York, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: IIF statement in query (with concat) (2000)

    Did you try the nz command?
    Perhaps
    NAME: [rank] & " " & [firstname] & " " & iif(NZ([middleinitial],0) = 0, [lastname] & " " & Right(Format([ssn],"@@@ @@ @@@@"),11) & "/" & [mos], ". " & [lastname] & " " & Right(Format([ssn],"@@@ @@ @@@@"),11) & "/" & [mos])
    Don
    <img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18> <img src=/S/flags/NewYork.gif border=0 alt=NewYork width=30 height=18> "Life on Earth is expensive, but at least it includes a free trip around the Sun."

  6. #6
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: IIF statement in query (with concat) (2000)

    If [middleinitial] is null it does not need to be omitted, because there is nothing to omit.

    What needs to be omitted is the ". " after middleinitial when middle initial is null.

    NAME: [rank] & " " & [firstname] & " " & iif(isNull([middleinitial],"",[middleinitial] & ". ") & [lastname]

    Ie. if middle injitial is null, put in an empty string "", other wise put in middle initial followed by ". "

    or

    NAME: [rank] & " " & [firstname] & " " & nz([middleinitial[)& iif(isNull([middleinitial],"", ". ") & [lastname]


    But you need to sure that middle initial really is null, rather than just ""
    Regards
    John



  7. #7
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: IIF statement in query (with concat) (2000)

    John, thanks for your help, is there a way to catch "" and null in "one fell swoop"?
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

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

    Re: IIF statement in query (with concat) (2000)

    For example like this:
    <pre>IIf(Nz([MiddleInitial],"")="", ..., ...)
    </pre>

    The Nz function converts Null values to "", so you can test against <code>""</code>.

  9. #9
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: IIF statement in query (with concat) (2000)

    Actually, on reflection, I would be inclined to do this in two steps:

    Create a calculated field:

    middleinitial2: iif(len(nz([middleinitial],"")) >0, [middleinitial]& ". ", "")

    By combining Len with NZ you can deal with both "" and null at the one time.

    Doing it in two steps makes it easier to debug.

    Then just include the field [middleinitial2] in the query
    Regards
    John



  10. #10
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: IIF statement in query (with concat) (2000)

    There is a trick you can use that involves the + operator, since any value + Null = Null. Try this:

    <code>NAME: [rank] & " " & [firstname] & " " & ([middleinitial] + ". ") & [lastname]</code>

    This will return a null if there is no middle initial, and the + insures that the entire middleinitial expression returns a null, which then concatenates nicely using the & operator.
    Charlotte

  11. #11
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: IIF statement in query (with concat) (2000)

    Old way: [firstname] & " " & [middleinitial] & ". " & [lastname]

    Try this: [firstname] & " " & [middleinitial] + ". " & [lastname]


    Note use of the + sign. In addition, adding anything to Null results in a Null, and the + operater is evaluated before the & operater.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

Posting Permissions

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