Find and replace tabs from data

[mentose457]

Hi everyone! I’ve got a few questions for you access experts out there.



I have data in column that has tabs in it. Which is a problem because I have to export the data as a tab delimited text file. So my question is how can I find and replace the tabs in a column and replace them with a space?

BTW, I run Access 2010 on Win 7 64bit.

Your responses are much appreciated.

[weekend00]

Use update query to do the replacement:

update [yourtable] set [fieldname]=replace([fieldname],chr(9)," ")
where instr(1,[fieldname],chr(9))>0

[mentose457]

Use update query to do the replacement:

update [yourtable] set [fieldname]=replace([fieldname],chr(9)," ")
where instr(1,[fieldname],chr(9))>0

This it the what the query column looks like now:

v_products_description_1: [Description] & " " & [Warranty] & " " & Chr(13) & Chr(10) & Chr(13) & Chr(10) & [RestrictedAreas]

can i use the solution you posted above in this situation?

Thanks

[weekend00]

I don't understand what you want. in the first post you said you want to replace all tabs to space, and in the last post you gave me an expression joining some fields.

[mentose457]

I don't understand what you want. in the first post you said you want to replace all tabs to space, and in the last post you gave me an expression joining some fields.

Sorry, I should have added that the [Description] column is the one with the tabs that need to be removed.

So do I have to do a query and replace the tab with a space like in your solution above then do another query with the expression I posted above? Or can i do this:

v_products_description_1: ([Description] =replace([Description],chr(9), " ") where instr(1,[Description],chr(9))>0) & " " & [Warranty] & " " & Chr(13) & Chr(10) & Chr(13) & Chr(10) & [RestrictedAreas]

[mentose457]

Bumpy bump bump.

[weekend00]

you don't need to update, just use select query:
v_products_description_1: replace([Description],chr(9), " ") & " " & [Warranty] & " " & Chr(13) & Chr(10) & Chr(13) & Chr(10) & [RestrictedAreas]

[mentose457]

you don't need to update, just use select query:
v_products_description_1: replace([Description],chr(9), " ") & " " & [Warranty] & " " & Chr(13) & Chr(10) & Chr(13) & Chr(10) & [RestrictedAreas]

Thanks for your help! Ill give it a try.

[mentose457]

Worked great! Thanks again for your help.

[weekend00]

you are welcome.