Cleaning Fields - Unprintable characters

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello All, I am having problems when I import text files into Access and then running a query to clean the text. for expample: PARTID column have some clean text and some unclean text (square small boxes at the beginning of some Part ID's) How can i get rid of these? Currently I have to export to Excel and then use the Clean(PARTID) function. Your help would be greatly apprceiated.
 
The boxes indicate that those are undisplayable characters. May be line
feeds or carriage returns. You could (if you're using ACCESS 2002 or higher,
or if you're using ACCESS 2000 and have SP6 update to Jet 4.0 engine) use
Replace function to strip those characters from the text string as you
update the data from the imported text file. To do this, you'll need to
identify what the character is. You can do this by running a Select query
that would look something like this:

SELECT PartID, Asc(Left([PartID, 1)) AS CharNumber
FROM TableName
WHERE Left([PartID]) Not Like "[A-Z0-9]";

This will show you the ASCII number of the nonalphanumeric character so that
you will know what to use in the Replace function.

To strip the strange character in the update query (alternatively, you could
use an append query if you plan to copy the data into a new, permanent
table):

UPDATE TableName
SET PartID = Replace([PartID], Chr(#), "");


where the # character is to be replaced by the number that you get for the
ASCII number in the first query.

--
Ken Snell
<MS ACCESS MVP>


Ray said:
Hello All, I am having problems when I import text files into Access and
Click to expand...
then running a query to clean the text. for expample: PARTID column have
some clean text and some unclean text (square small boxes at the beginning
of some Part ID's) How can i get rid of these? Currently I have to export to
Excel and then use the Clean(PARTID) function. Your help would be greatly
apprceiated.
 
Hello All, I am having problems when I import text files into Access and then running a query to clean the text. for expample: PARTID column have some clean text and some unclean text (square small boxes at the beginning of some Part ID's) How can i get rid of these? Currently I have to export to Excel and then use the Clean(PARTID) function. Your help would be greatly apprceiated.
Click to expand...

It would help if you could ascertain (perhaps in Excel, using the
HEX() function) what these characters actually represent. Some
possibilities are Chr(13) or Chr(10) - carriage return and line feed
respectively - or Chr(00) - NUL.

If you can determine it, you can run an Update query updating PartID
to

Replace(PartID, Chr(13), "")

or whatever the garbage character might actually be.
 
This Helps but I can't get the replace function to work. I don't have an update to Jeg Engine 4.0. is there another function I could use?
 
Hello Again, I found the character number. Its chr (12) but now I can't get the replace funtion to work in MS Access. I just found out I have two versions of MS Jet 4.0 on my comptuer and still can't get the replace function to work. is there another function I can use besides "Replace"?
version: 4.00.7328.
version: 4.00.8618.0
 
You can write your own public function (put it in a module named
basFunctions) and then use this function in place of Replace in the query:

Public Function RReplace(strOriginal As String, strFind As String,
strSubstitute As String) As Variant
RReplace = Replace(strOriginal As String, strFind As String,
strSubstitute As String)
End Function


--
Ken Snell
<MS ACCESS MVP>

Ray said:
Hello Again, I found the character number. Its chr (12) but now I can't
Click to expand...
get the replace funtion to work in MS Access. I just found out I have two
versions of MS Jet 4.0 on my comptuer and still can't get the replace
function to work. is there another function I can use besides "Replace"?:
 
Back
Top