Thanks Thanks:  0
Results 1 to 6 of 6

Thread: Comma or State showing up in Concatenated field

  1. #1
    Board Regular
    Join Date
    May 2005
    Posts
    541
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Comma or State showing up in Concatenated field

    I have a formula that combines the city, state and Zip fields in one field called CSZ. I want to show nothing in that field if there isn't an address provided

    But, if the address, city, state, or zip fields are null, I will get a ", " followed any field that may have something (e.g. ", CA").

    What am I doing wrong with the formula below:

    IIf(Not IsNull([Address]),[City] & ", " & [State] & " " & [Zip]," ")

    I tried putting the '" "' result first (i.e. ISNull([Address]), but I got the same result.

    It seems that it is including the comma if the address field is empty for some reason.

    Thank you for your help,

    Michael

  2. #2
    Board Regular
    Join Date
    May 2005
    Posts
    541
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Comma or State showing up in Concatenated field

    I think I found the problem.

    IIf(Not IsNull([Address]),[City] & ", " & [State] & " " & [Zip]," ")

    should be

    IIf([Address]<> " ",[City] & ", " & [State] & " " & [Zip]," ")

    This brings up the question, why doesn't Not Null or Is Not Null work in the situation. Is this because I have a text field?

    Michael

  3. #3
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    16,101
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Comma or State showing up in Concatenated field

    Code:
    IIf([Address]<> " ",[City] & ", " & [State] & " " & [Zip]," ")
    This formula has a space as the condition for the test (Address is not equal to space character).
    A space character is not the same as null. Even an empty string "" is not the same as null (as well as not the same as an empty string).

    So ... null tests only work for null, is the short answer. Your data has to be actually null. But unfortunately in MSAccess null fields don't look any different from fields with empty strings or only spaces in them, so visually you cannot tell the difference.

    You can find the real nulls this way:

    Code:
    Select * from Table1 where [Address] is null
    Also try:
    Code:
    Select * from Table1 where [Address] = ""
    Select * from Table1 where [Address] = " "

    Using: Office 2010/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  4. #4
    Board Regular
    Join Date
    May 2005
    Posts
    541
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Comma or State showing up in Concatenated field

    thank you as always Joe (that is your name isn't it). The code is you can is SQL, correct?

  5. #5
    Board Regular
    Join Date
    Jun 2015
    Location
    Ontario, Canada
    Posts
    1,618
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Comma or State showing up in Concatenated field

    Code:
    IIf(Not IsNull(
    Also depends on where you're using IsNull or Is Null. IsNull is a VBA function and can't be used in a query criteria field. Is Null is for sql. In the case of checking for not being null, then Not IsNull (vba) or Not Is Null (sql). Your example suggests to me that this is for a query, otherwise the use of double quotes around the concatenation parts would be an issue. So I think your problem is the use of a vba function in a query because the rest of it looks OK to me as a concatenated query expression. Also, IF something <> "" is OK to check for empty strings, but it would be unwise to use this in place of checking for Null IMHO. Null is unknown and isn't the same as an empty string. You certainly cannot compare null to anything e.g. =, <>, >=, <=
    Tips for posting problems:
    1) "doesn't work" doesn't help. Post error message text/numbers and/or state what's happening.
    2) if posting code or sql, use code tags; specify on which code line errors occur, if applicable
    3) try to be specific; assume we know nothing about your issue - because we don't!

    Make all suggested changes in copies of your database or to its objects.

    "1 out of 1010 people understand binary. The other 1001 don't."

  6. #6
    Board Regular
    Join Date
    May 2005
    Posts
    541
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Comma or State showing up in Concatenated field

    Good point, thank you for the clarification. I'll remember that

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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