Omit Comma (1 Viewer)

Hello,

I was trying to format addresses for a report, but am having difficulty on one part. I need to be able to omit the comma between "city" and "state" if there is no "city" listed as happens sometimes with the foreign addresses. I tried the following but it did not work:

=Format_Address([Add 1],[Add 2],[Add 3],[City],[State],[Zip]) & IIf(IsNull([City]),Null,",")

Any advice would be greatly appreciated. Thanks very much.

Hi Lily

You want something along these lines:

=[Add 1]&", "&[Add 2]&", "&[Add 3]&", "&([City]+", ")&[State]&", "&[Zip]

HTH

Rich



[This message has been edited by Rich@ITTC (edited 02-03-2001).]

Rich,

Thank you for your reply and I almost have it...however, if I could trouble you further, if I use this expression:

=Format_Address([Add 1],[Add 2],[Add 3],[City],[State],[Zip])

it returns my address as:

123 Main Street
Anytown, Anystate 10101

which is how I need it, however, sometimes there is no city and then it returns an address as:

123 Main Street
, Anystate 10101

so I was looking for a way to omit the comma if no city exists and then just run Anystate 10101 flush left? Thanks again Rich for your help, it is appreciated very much.

Hi Lily

You will need to familiarise me with the Function you are using - Format_Address. How is this set up?

From what you describe in your follow up question you can already accept blanks in [Add 2] and [Add3] with the commas disappearing (or is this you just being economical with your example?!?). Also, how do you ensure that [City] (or if [City] is null/blank [State] always appears on the next line down?

Sorry ... I have only given you questions, not answers.


Rich

Rich,

Thank you, and I owe you the apology!! You are right, I did not convey the correct information here I don't think as there was a "module" written to format the addresses. I added the following to that:

If Not IsNull(City) Then 'if city is not blank, omit comma
Format_Address = Format_Address & City & ", " & State & " " & Zip
Else
Format_Address = Format_Address & State & " " & Zip
End If

End Function

...and it works now and omits the comma if no city is listed. My sincere thanks to you though for your time and advice, and again my apologies for not giving the right information to begin with.

Lily