Results 1 to 5 of 5
  1. #1
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    How to Code IIf to Test for Null or Space? (a2k (9.0.3821) SR-1 Jet 4.0)

    How to Code IIf to Test for Null or Space?
    a2k (9.0.3821) SR-1 Jet 4.0

    If the middle initial is space or null I don

  2. #2
    Lounger
    Join Date
    Jan 2003
    Location
    Herndon, Virginia, USA
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to Code IIf to Test for Null or Space? (a2

    Hi John,

    One solution is to not bother testing for the IsNull, just convert it to "":

    CopyToName = IIf(Nz(strMidInit,"") = "", _
    strFirstName & " " & strLastName, _
    strFirstName & " " & strMidInit & " " & strLastName)

    The other option is to nest the IIFs, but that's requires replication of strFirstName & " " & strLastName:

    CopyToName = IIf(strMidInit = "", _
    strFirstName & " " & strLastName, _
    IIf(IsNull(strMidInit), _
    strFirstName & " " & strLastName, _
    strFirstName & " " & strMidInit & " " & strLastName))

    Brent

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

    Re: How to Code IIf to Test for Null or Space? (a2k (9.0.3821) SR-1 Jet 4.

    Use <font face="Georgia">Trim(strMidInit & "")=""</font face=georgia> as test. You need only a single IIf then.

  4. #4
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to Code IIf to Test for Null or Space? (a2k (9.0.3821) SR-1 Jet 4.

    Hi Brent & Hans

    The following seem to work for me, it catches null field and space filled field.

    CopyToName = IIf(Trim(strMidInit & "") = "", _
    strFirstName & " " & strLastName, _
    strFirstName & " " & strMidInit & " " & strLastName)

    Thanks, John

  5. #5
    Lounger
    Join Date
    Jan 2003
    Location
    Herndon, Virginia, USA
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to Code IIf to Test for Null or Space? (a2

    I like Hans' suggestion as well (which is what you used). It catches 3 conditions (Null, empty string, and spaces) in one elegant little snippet of code, as opposed to mine which caught just two.

    Brent

Posting Permissions

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