Remove Tab char

[Freshman]

Hi all,

I've got a Excel SS with a field that appears to contain a number eg: 0059 with some trailing spaces.
I'm trying to get rid of the trailing spaces but trim or replace just don't do it.
When I copy the field to a notepad I get a " followed by a <Tab> and then 0059 followed by another <Tab> space and then another " at the end.

So it seems like there is a "hidden" tabs characters on either side.
How on earth do I get rid of those in an Access update query.

Thanks

 

[Freshman]

Answering my own question thanks to my buddy Google.
Replace([Field],Chr(9),"")

Cheers

 

[MarkK]

Tab character is Chr(9), so you can replace that with an empty string . . .

Replace(<string with tabs>, Chr(9), "")

 

[Freshman]

@Mark - thanks

 

[MarkK]

Actually, looks like I was a few minutes late with that solution. Glad you got it figured out!

 

[Freshman]

No sweat Buddy

 

[Galaxiom]

While on the subject of Tabs, there is a bug that will cause weird behaviour when Tabs are pasted into a control.

http://www.access-programmers.co.uk/forums/showthread.php?t=259512

 

[Freshman]

@Galzxiom - Thanks - that is exactly the problem I had to overcome. The 2 tabs on either side of the text are in the Excel spreadsheet that is how I import from a 3rd party Payroll software app.
It boggled my mind when I tried to get rid of what I though was spaces as you have no real way of seeing it is tabs while in Excel. It was only when I, almost by accident, copied the text to Notepad that I saw the tabs.
What a mess hey!
Was easy to fix once I knew what I had to replace as replacing a space does nothing to a tab

 

[Galaxiom]

It was only when I copied the text to Notepad that I saw the tabs.

That is exactly what I did when nothing was making sense in Access.

 

[Wimboman]

Interesting! I had a problem with new values entered into a table not sorting correctly. It's only while I was posting a question in this forum that I saw that the old data (imported from FoxPro) had double quotes and tabs inserted.

Looking at the data in Excel, or pasting it into Word (keeping formatting) showed nothing weird. Pasting into Notepad showed something strange. Pasting into Word as plain text enabled me to identify the extraneous characters.

 

[gemma-the-husky]

note also that trim() and its buddies ltrim() and rtrim() will remove unnecessary leading and trailing spaces from text fields

 

[Pat Hartman]

If you might have strange characters in a string and you don't know what they might be, you have to loop through the variable, character by character and copy out the valid characters - letters, numbers, and any special characters that are allowed - into a separate variable. Then use the extracted variable to continue processing.