removing carriage return from records 1

[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 can only handle a single line in each field.
Is there a way to remove the carriage returns and replace with a comma?
I'm happy for the solution to be either within Access or in the exported version in Excel.
Thanks in advance
Jim

 

[Binnit]

Jim
Suggest you build a query and concatanate all the address fields in to one column (if thats what you need in the next application)

You can then either export the query as a spreadsheet, or, open the query, copy and paste the records in to Excel or wherever.



Happiness is...not getting what you want but wanting what you have already got

 

[Welshbird]

Could you not export these with "" around? Or does this not help at all.

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[greenfibres]

Hi Binnit
unfortunately you have missunderstood the scenario.
1 field (called Address_Line_2) has information stored in multiple lines, created by using carriage return - ie looks like this
Keepers Cottage
Brooklands Lane

And I can't import it like that.

It needs to be
Keepers Cottage, Brooklands Lane

Does that make it clearer?


Hi willif
I'm afraid I don't have a clue as to what you mean.

Step by step would be nice!

thanks for replying
Jim

 

[Welshbird]

I was wondering if you could export this table and specify that this field would be enclosed in double quotes to enable the import, but this won't remove the carriage returns; just hide them when importing the data.

So my suggestion won't help I'm afraid.

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[TomCologne]

Jim,

Since you cross-posted your question in the Access Other Topics Forum, did you at least check Remou's reply there?

TomCologne

 

[Joycelet]

Hi

Not sure if you have resolved this - but you need to look at the clean option, which will remove non printable characters and also the find and replace. Find all character returns and replace with a ","

Hope this helps

Nicola

 

[greenfibres]

Hi, still no joy with this one.

 

[greenfibres]

Thanks for the 'clean' idea, I didn't know it existed.

Finally resolved my issue with the following code

=CLEAN(SUBSTITUTE(cellref,CHAR(13),", "))