Comma or State showing up in Concatenated field

[bearcub]

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

 

[bearcub]

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

 

[xenou]

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:

Select * from Table1 where [Address] is null

Also try:

Select * from Table1 where [Address] = ""
Select * from Table1 where [Address] = " "

 

[bearcub]

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

 

[Micron]

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. =, <>, >=, <=

 

[bearcub]

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