removing carriage return from records

[greenfibres]

Hi
I have an Access '97 table that has addresses of contacts.
Some of the fields have been populated from the Form that is bound to this Table, with second and sometimes third lines of text.
I need to export these addresses to a totally different application (probably via Excel) but this new application cannot handle more than single lines of records.
Is there a way to remove the carriage returns and replace with a comma?
Thanks in advance
Jim

 

[Remou]

It should be possible to run an update query, for example:

[tt]UPDATE Houses SET Houses.Address = FindAndReplace([Address],(Chr(13)+Chr(10)),", ")[/tt]

The function FindAndReplace is from:

http://www.mvps.org/access/strings/str0004.htm

 

[greenfibres]

Hi Remou
Sorry, it took me a while to get back to this

here's my code
UPDATE Cust SET Cust.[Billing Address 2] = FindAndReplace([Billing Address 2],(Chr(13)+Chr(10)),", ")

Unfortunately I just get the following message:-

undefined funtion 'FindAndReplace' in expression

Please note I'm using Access97 if that makes a difference

 

[Remou]

You need to copy the code from the link I posted into a module in your database.

 

[WinblowsME]

Try

UPDATE Cust SET Cust.[Billing Address 2] = FindAndReplace(FindAndReplace([Billing Address 2],Chr(10),", "), Chr(13), ", ");

 

[Remou]

WinblowsME
Why the extra level of complication?

 

[WinblowsME]

INSERT INTO tblData ( Data )
VALUES ("A" & chr(10) & "B" & chr(13) & "C");

http://msdn2.microsoft.com/en-us/library/bt3szac5(VS.80).aspx