UtterAccess HomeUtterAccess Wiki

Welcome Guest ( Log In | Register )

Edit Discussion
> Removing Non-Printable Characters from Data    
(Redirected from Carriage Return)
Removing Non-Printable Characters from Data

Related Content:
    ASCII Codes


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?"

Contents

Definition



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

and

CHR(10) - The Line Feed

... and they have to be in that order.


Examples

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

or

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:

CODE
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:

vbCrLf

(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:

CODE
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:

ABC[]123

(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 ("ABC[]123", 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:

CODE
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:

  • CHR(0) - The NULL character
  • CHR(8) - The BACKSPACE character
  • CHR(9) - The TAB character
  • CHR(27) - The ESCAPE character

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.

CODE
([Address1]+Chr(13)+Chr(10)) & ([Address2]+Chr(13)+Chr(10)) & ([city]+', ') & [st] & '  ' & [zip] & ('-'+[ZipExt])

-->
123 Main St
Denver, CO  80201

-->
4578 Oak Terrace
Suite 212
Chicago, IL  60601-1234

NOTE: chr() is not supported in 2010 web databases ... yet?


Conclusion

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.

Edit Discussion
Thank you for your support!
This page has been accessed 10,084 times.  This page was last modified 12:33, 5 February 2012 by Jack Leach. Contributions by strive4peace2010 and Walter Niesz  Disclaimers