Replace commas with hard return in a query field

[Ceebaby via AccessMonster.com]

Hi Everyone

I am trying to update an address field in a query which has the address on 1
line. Each part the of the address is seperated by commas. I wish to replace
the commas with hard returns.

eg 123 any street, london, PO78 T89

and change it to

123 any street
London
PO78 T89

It will be used to do a mail merge for address labels.

I tried on the field for the update query test: replace(",", ], Chr(13) &
Chr(10)))

but I ended up with records that only had a comma.

Can this be done or do I manually have to insert hard returns?

Thanks for any help any one can give.

Cheers
Ceebaby

 

[Stefan Hoffmann]

hi,

I tried on the field for the update query test: replace(",", ], Chr(13) &
Chr(10)))
but I ended up with records that only had a comma.
Can this be done or do I manually have to insert hard returns?

Yes, your soluction is basically correct. It should read:

test: Replace([yourField], ",", Chr(13) & Chr(10))


mfG
--> stefan <--

 

[Ceebaby via AccessMonster.com]

Hi Stefan

Many thanks for your response, I realised this just before I saw your post.
Thanks for your help and have a good rest of the day.

Cheers
Ceebaby
London

hi,

I tried on the field for the update query test: replace(",", ], Chr(13) &
Chr(10)))
but I ended up with records that only had a comma.
Can this be done or do I manually have to insert hard returns?

Yes, your soluction is basically correct. It should read:

test: Replace([yourField], ",", Chr(13) & Chr(10))

mfG
--> stefan <--