Non-printing character in table (1 Viewer)

[RogerCooper]

I have an Access native table which contains information cut & pasted from a website. When that happens, sometimes you get spaces that can be easily trimmed. Other times I get weird things like this.

Image from screen

Cutting and pasting the rows
Query1 Query1
Query1 Query1

Invoice Number	Last Character	Asc Value Last	Field Length	First Character	Asc Value First
5334113798	8	56​	11​		9​
5334113798	8	56​	10​	5	53​

Here is the query code

SELECT [UPS Supply Chain Bills].[Invoice Number], Right([Invoice Number],1) AS [Last Character], Asc(Right([Invoice Number],1)) AS [Asc Value Last], Len([INvoice Number]) AS [Field Length], Left([Invoice Number],1) AS [First Character], Asc(Left([Invoice Number],1)) AS [Asc Value First]
FROM [UPS Supply Chain Bills]
WHERE ((([UPS Supply Chain Bills].Amount)=432.88));

It looks like a <TAB> character has gotten in. How can I remove it automatically or prevent it from be entered in the first place?

 

[theDBguy]

Not sure about preventing if you're pasting data. Otherwise, you can use an UPDATE query to fix it. Look up both the Asc() and Chr() functions.

 

[plog]

You always want to find a general rule and work with that. You've only given us 1 example but using that I see this:

The invoice number is the last 10 characters of the pasted value.

If that's true, a simple Right() will get those 10 characters and you don't have to worry about looking for specific ascii values or testing the length of the string. If that's not true, try and find a general rule that does work and then code for that.

 

[ebs17]

If you know what you want to eliminate:

Replace("Yourstring", Chr(9), "")

If you don't know exactly what you need to eliminate, but only want to keep certain characters such as letters, digits, underscores, regular expressions, e.g. with a search pattern, will help you...

[^a-zA-Z_0-9]

 

[RogerCooper]

If you know what you want to eliminate:

Replace("Yourstring", Chr(9), "")

If you don't know exactly what you need to eliminate, but only want to keep certain characters such as letters, digits, underscores, regular expressions, e.g. with a search pattern, will help you...

[^a-zA-Z_0-9]

Thank you, first option worked well.

 

[gemma-the-husky]

It may not always be safe to just remove the rightmost character.
You really need to find out what is causing this.

chr(9) is a tab, so someone or something is inserting a tab at the start of the string.

 

[RogerCooper]

When you copy and paste from a website, you can get strange characters. I don't see any way of preventing that.

 

[arnelgp]

you use a Form for your table and add call a function that will "clean" your string
before it got saved.