Trimming commas from combined fields

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
You can generally use + rather than & to remove the comma:

=[AddressLine1]& ", " &[Town]& ", " + [County]& ", " &[PostCode]
 
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
 
Thanks Duane. I'll try that. Cheers, JohnB

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

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

--
Duane Hookom
MS Access MVP


JohnB said:
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
Click to expand...
Click to expand...
 
Thanks Al. I'll have a look at you sugesstion, along with Duanes. Cheers, JohnB

Al Camp said:
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

JohnB said:
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
Click to expand...
Click to expand...
 
Back
Top