Comma or State showing up in Concatenated field

bearcub

Well-known Member
Joined
May 18, 2005
Messages
701
Office Version
  1. 365
  2. 2013
  3. 2010
  4. 2007
Platform
  1. Windows
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
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
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
 
Upvote 0
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] = " "
 
Upvote 0
thank you as always Joe (that is your name isn't it). The code is you can is SQL, correct?
 
Upvote 0
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. =, <>, >=, <=
 
Upvote 0
Good point, thank you for the clarification. I'll remember that
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,025
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top