Removing Non-Printable Characters from Data
As we work with data from more and more sources, it soon becomes apparent that we have to expect anything. The way one system interprets data can be very different than the next. Yet we still need to be able to make them understand each other.
One of the most commonly asked questions is, "How can I remove carriage returns from my data?"
chr(13) and chr(10)
First, we need to understand what makes up a carriage return. Carriage returns are known by many names, "line breaks", "line feeds", "hard returns", "return characters", etc. For the purposes of this article though, we are talking about the character(s) that tell a program to push text onto the next line.
The most common set of characters that make up a carriage return are the carriage return and line feed pair. That's right, there are two characters that tell Access and most other programs to split data onto two lines. They are made up of two character codes in combination.
CHR(13) - The Carriage Return
CHR(10) - The Line Feed
... and they have to be in that order.
So how can we work with them in Access? Understand that every character that we see on the screen can be represented by an ASCII Code. Essentially this is a way to describe a character to a computer without using the character itself. For instance, a capital "A" can be represented by CHR(65), capital "B" by CHR(66), capital "C" by CHR(67), etc. The same goes for numbers. So instead of typing "ABC123", we can use:
CHR(65) & CHR(66) & CHR(67) & CHR(49) & CHR(50) & CHR(51)
...and the computer will know what we are talking about.
So when Access sees a piece of data with a carriage return, it sees:
ABC<carriage return><line feed>123
CHR(65) & CHR(66) & CHR(67) & CHR(13) & CHR(10) & CHR(49) & CHR(50) & CHR(51)
So now that we understand what they are made of, we can easily write some code to remove them. By using the REPLACE() function we can replace carriage returns with an empty string, a space, or whatever characters we want, like:
REPLACE([MyField], CHR(13) & CHR(10), "")
This will work in a query or in VBA. VBA makes things a little easier by also giving us a constant that represents CHR(13) & CHR(10) without having to type it out. It is:
(I'll let you use your deductive reasoning skills to figure out what that stands for. ) So in VBA we could just as easily use:
MyVariable = REPLACE(Me.MyField, vbCrLf, "")
But remember, not all systems use CHR(13) & CHR(10) to create carriage returns. Data coming from UNIX and Unix-like systems (Linux, Mac OS X, BSD), even Excel, are notorious for only using a single CHR(10) line feed (vbLf) character. If we are unsure what the character is, we can perform a few tests. Most of the time Access will represent a non-printable character by a small square embedded in the text. So we might see something like:
(Note in the above example I used two square brackets to help visualize what a small square would look like in Access)
We need only to ask the computer for the CHR code to find out what the character is. How do we do that? By using the ASC() function. This function is essentially the reverse of the CHR() function. It would look like:
ASC (MID ("ABC123", 4, 1) )
... or in plain English, "What is the ASCII Code of the fourth character in the given string?"
In a query it may look like:
ASC(MID([MyField], 4, 1))
The result that is returned will tell us what number to use in the CHR() function in order to replace it with a harmless empty string.
You may be surprised at what characters you find! The most common are:
For a full listing of ASCII Codes and their meanings please see Access Help or an online list.
Line Feed in field expressions of an SQL statement
If you want a line feed in data to be rendered BY SQL, you will use chr(13) & chr(10) ... not the vbCrLf constant.
([Address1]+Chr(13)+Chr(10)) & ([Address2]+Chr(13)+Chr(10)) & ([city]+', ') & [st] & ' ' & [zip] & ('-'+[ZipExt])
123 Main St
Denver, CO 80201
4578 Oak Terrace
Chicago, IL 60601-1234
NOTE: chr() is not supported in 2010 web databases ... yet?
Hopefully this discussion has made it a little easier to understand how to find and eliminate special characters in your data. It takes a little practice, but soon you'll be handling them with ease.
|This page has been accessed 12,800 times. This page was last modified 12:33, 5 February 2012 by Jack Leach. Contributions by strive4peace2010 and Walter Niesz Disclaimers|