Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Nov 2001
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Creating an Expression with Variable Null Values (2000)

    I am trying to create an expression that takes three fields (Member First Name, Member Last Name, and Spouse First Name) and creates the following MEMBERLAST, MEMBERFIRST and SPOUSEFIRST

    It works except when the spouse first name field is Null, insted of returning the Members Last and First Name it does not return anything. If all three fields contain values it works fine. How do I get around the one with no Spouse Name???

    Here is what I have right now:

    Expr1: [qryActiveMembers]![M_LastName]+", "+[qryActiveMembers]![M_FirstName]+" and "+[qryActiveMembers]![S_FirstName]

  2. #2
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Edmonton, Alberta, Canada
    Posts
    326
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating an Expression with Variable Null Values (2000)

    Don't use +, use &. Both have the same effect when used with strings except when one of those strings is null.

    null + "XYZ" = null
    null & "XYZ" = "XYZ"

  3. #3
    New Lounger
    Join Date
    Nov 2001
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating an Expression with Variable Null Values (2000)

    Thanks! One last question how do I make the "and" only show up if the spouse name is not null.

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Edmonton, Alberta, Canada
    Posts
    326
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating an Expression with Variable Null Values (2000)

    [qryActiveMembers]![M_LastName] & ", " &[qryActiveMembers]![M_FirstName] & iif(not isnull([qryActiveMembers]![S_FirstName] )," and " & [qryActiveMembers]![S_FirstName] ,"")

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

    Re: Creating an Expression with Variable Null Values (2000)

    A simpler way exploits the behavior of the + operator in concatenation:

    [qryActiveMembers]![M_LastName] & ", " &[qryActiveMembers]![M_FirstName] & ( " and " + [qryActiveMembers]![S_FirstName] )

    That will only concatenate the "and" and the S_Firstname if S_Firstname is not null.
    Charlotte

Posting Permissions

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