replace vbCrLf with ,

[Tom]

ANy advice/suggestions on the following would be appreciated

Using Access 2002 a text field in a table a clients mail address in the
format of:

Street
Town
PostCode
County

there is now a requirement, in a new report, to have that mail address
displayed in the format of:

Street, Town, PostCode, County

For this new format how can the vbCrLf be replaced with a ,?

TIA

Tom

 

[Allen Browne]

I take it you have a single field named (say) "Address", that contains
several lines?

If so you can set the Control Source of the text box on your report to
something like this:
=Replace([Address], Chr(13) & Chr(10), ", ")

Make sure the Name of this text box is not the same as the name of a field,
e.g. it must not be called Address.

 

[Tom]

Brilliant Allen
thanks very much

Tom

I take it you have a single field named (say) "Address", that contains
several lines?

If so you can set the Control Source of the text box on your report to
something like this:
=Replace([Address], Chr(13) & Chr(10), ", ")

Make sure the Name of this text box is not the same as the name of a
field, e.g. it must not be called Address.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

ANy advice/suggestions on the following would be appreciated

Using Access 2002 a text field in a table a clients mail address in the
format of:

Street
Town
PostCode
County

there is now a requirement, in a new report, to have that mail address
displayed in the format of:

Street, Town, PostCode, County

For this new format how can the vbCrLf be replaced with a ,?

 

[Arvin Meyer [MVP]]

Try:

[Street] & ", " & [Town] & ", " & [PostCode] & ", " & [County]
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access