Remove Carriage Return in Access Query from Read Only Linked SQL table

I need to remove carriage returns from a table via a query which is in turn being converted in to a csv file ready for import.  As you can imagine a carriage return causes major problems when trying to import.

I am using the following method Replace([Street Name],Chr(10),", ")

This does work but for some reason converts the carriage return to a ? in a box symbol.  When reviewing the CSV, the carriage return is still there.

Any help will be greatly appreciated.

Thanks

Simon
jigsol-simonAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

 
Rey Obrero (Capricorn1)Commented:
try

Replace([Street Name],chr(13) & Chr(10),", ")
0
 
jigsol-simonAuthor Commented:
tried that and it does not do anything, ie it keeps the carriage return in.  Not sure if this helps but when I have the symbol in Excel and I perform the function =CLEAN(A1), it removes the character.  There is no such function in Access and not sure if I am on to something or not.

Thanks

Simon
0
 
webwyzsystemsCommented:
Depending on data source, you might actually need to replace both CHAR(10) and CHAR(13). Remember that some formats use /r/n to indicate line endings.
Did you try the replace with a Chr(32) rather than a literal ","?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I think Cap's got one too many "" in there:

Replace([Street Name],chr(13) & Chr(10),")

or maybe this:

Replace([Street Name], vbCrLf, "")

vbCrLf is the "Carriage return, Line feed" constant.
0
 
Rey Obrero (Capricorn1)Commented:
try  chr(9) is tab

replace(replace(replace(Replace([Street Name],chr(13) & Chr(10),", "),chr(13),", "),chr(10),", "), chr(9),", ")
0

Experts Exchange Solution brought to you by ConnectWise

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
 
Rey Obrero (Capricorn1)Commented:
the replacement character is comma and a space { ", " }
0
 
jigsol-simonAuthor Commented:
Absolute genius.

Thank you so much.

Simon
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.