Go Back   Access World Forums > Microsoft Access Discussion > Reports

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 02-03-2001, 06:50 AM   #1
Lily
Newly Registered User
 
Join Date: Sep 2000
Posts: 42
Thanks: 3
Thanked 0 Times in 0 Posts
Lily
Omit Comma

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.

Lily is offline   Reply With Quote
Old 02-03-2001, 07:54 AM   #2
Rich@ITTC
Senior Member
 
Join Date: Jul 2000
Location: Bournemouth, Dorset, UK
Posts: 237
Thanks: 0
Thanked 0 Times in 0 Posts
Rich@ITTC
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@ITTC is offline   Reply With Quote
Old 02-03-2001, 08:38 AM   #3
Lily
Newly Registered User
 
Join Date: Sep 2000
Posts: 42
Thanks: 3
Thanked 0 Times in 0 Posts
Lily
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.

Lily is offline   Reply With Quote
Old 02-05-2001, 05:37 AM   #4
Rich@ITTC
Senior Member
 
Join Date: Jul 2000
Location: Bournemouth, Dorset, UK
Posts: 237
Thanks: 0
Thanked 0 Times in 0 Posts
Rich@ITTC
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@ITTC is offline   Reply With Quote
Old 02-05-2001, 03:33 PM   #5
Lily
Newly Registered User
 
Join Date: Sep 2000
Posts: 42
Thanks: 3
Thanked 0 Times in 0 Posts
Lily
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

Lily is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump




All times are GMT -8. The time now is 05:17 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World