Trimming commas from combined fields

[Guest]

Hi.

If I have the following as a fields control source in a report, is there a
way of stopping the next comma appearing if any supplying field, except the
last one, is empty?

=[AddressLine1]& ", " &[Town]& ", " &[County]& ", " &[PostCode]

e.g if a particular record has no County, I get:

42 Fenchurch St, Wanchester, , ET4 4SA

How do I stop double commas appearing?

Thanks, JohnB

 

[Duane Hookom]

You can generally use + rather than & to remove the comma:

=[AddressLine1]& ", " &[Town]& ", " + [County]& ", " &[PostCode]

 

[Al Camp]

John,
Try this for the Address and Town, and you carry that through with the
other elements...
= [AddressLine1] & IIF(IsNull([Town]), "", ", " & [Town]) &
IIF(IsNull([County])....... etc

hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

 

[Guest]

Thanks Duane. I'll try that. Cheers, JohnB

You can generally use + rather than & to remove the comma:

=[AddressLine1]& ", " &[Town]& ", " + [County]& ", " &[PostCode]

--
Duane Hookom
MS Access MVP

Hi.

If I have the following as a fields control source in a report, is there a
way of stopping the next comma appearing if any supplying field, except
the
last one, is empty?

=[AddressLine1]& ", " &[Town]& ", " &[County]& ", " &[PostCode]

e.g if a particular record has no County, I get:

42 Fenchurch St, Wanchester, , ET4 4SA

How do I stop double commas appearing?

Thanks, JohnB

 

[Guest]

Thanks Al. I'll have a look at you sugesstion, along with Duanes. Cheers, JohnB

John,
Try this for the Address and Town, and you carry that through with the
other elements...
= [AddressLine1] & IIF(IsNull([Town]), "", ", " & [Town]) &
IIF(IsNull([County])....... etc

hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

Hi.

If I have the following as a fields control source in a report, is there a
way of stopping the next comma appearing if any supplying field, except
the
last one, is empty?

= [AddressLine1] & ", " &[Town]& ", " &[County]& ", " &[PostCode]

e.g if a particular record has no County, I get:

42 Fenchurch St, Wanchester, , ET4 4SA

How do I stop double commas appearing?

Thanks, JohnB