Removing CR, line breaks, and tabs from data

[susanmgarrett]

I'd like to run a search and replace on a query result to remove carriage returns, linek breaks and tabs from the data. I know that MSWord has character aliases that will allow you to do a search and replace - does Access have an equivalent for these?

Thanks!

Susan

 

[RV]

If you're on Access 2000 or higher, you could use the Replace function.

RV

 

[susanmgarrett]

Replace what?

I would love to use the replace function (Access 2003), but have no idea what characters to enter into the find field to find and replace. I know tab in word is ^t and paragrah mark is ^p and line break is ^l. Are there equivalents in Access?

Thanks.

Susan

 

[RuralGuy]

Tab is VT chr(9), ^L is FF chr(12), ^T is DC4 chr(20), ^P is DLE chr(16), CR chr(13) and LF chr(10).

 

[SpursGirl]

How does the replace function work in order to eliminate carriage returns and line breaks?

 

[RuralGuy]

Hi SpursGirl,
Just fine I think.

 

[SpursGirl]

How do you use the replace functions?

What I mean is...How do I right the code for this?

 

[RuralGuy]

Replace([YourControlName], vbCrLf, "") should eliminate the vbCrLf sequence in [YourControlName]. Does that answer your question or do you need further assistance. It is VBA help.

 

[SpursGirl]

VBA Help

I'm sorry but I have never used Visual Basic in Access and don't even know where to start but thanks for your response.

 

[RuralGuy]

In what are you trying to eliminate the carriage returns and line feeds? Are you on a form?

 

[SpursGirl]

Hi,
I am receiving a text file out of a core system that is created with carriage returns/line feeds. When I import the file into a table a blank record is created because of the carriage return/line feed. I could delete the blank records in a query but would perfer to import a clean file. Do you have any suggestions?

 

[RuralGuy]

I haven't imported any text files before but vbCrLf usually marks the end of a record in a comma delimited or tab delimited csv file. Can't you handle this in the input specifications? What method are you using to import this file?