How can I clean imported ASCII character "CHAR(10)" data in MS Ac.

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

Guest

When i import data from my non -MS office database into MS Access tables i
get a ASCII character ("CHAR(10)") that i can not clean. Can anyone help me?
 
An update query calling the Replace() function should do it ...

UPDATE YourTable SET YourField = Replace(YourField, Chr$(10), "") WHERE
YourField IS NOT NULL
 
Brendan, Thanks! This works great!

Another question, is there any way to count these characters "Chr$(10)" in
the record? For example, if a record holds 5 values and each value is
followed by a carraige return:
1154 Chr$(10) 1258 Chr$(10) 1350 Chr$(10) 4587 Chr$(10) 5500
In this case theres 4 Chr$(10) characters.
How can i count how many "Chr$(10)" characters or Values are in the that
record?
 
SELECT Len([TestText])-Len(Replace([TestText],Chr$(10),"")) AS NumChars
FROM tblTest
WHERE (((tblTest.TestText) Is Not Null));

The length of the string, minus the length of the string with the specific
character replaced by an empty string (two double quotes with nothing, not
even a space, between them) gives the number of that character contained in
the string. The Is Not Null test is to avoid the error that would be raised
if we passed a Null value to the Replace function, which expects a string.
 
Brendan, This is exactly what i need it. I was using the same Logic in Excel
( Formula=len(Cell)-Len(Clean(Cell)) ) i just wasnt sure how to do it in
Access.

Thanks a Million,
Omar Menjivar

Brendan Reynolds said:
SELECT Len([TestText])-Len(Replace([TestText],Chr$(10),"")) AS NumChars
FROM tblTest
WHERE (((tblTest.TestText) Is Not Null));

The length of the string, minus the length of the string with the specific
character replaced by an empty string (two double quotes with nothing, not
even a space, between them) gives the number of that character contained in
the string. The Is Not Null test is to avoid the error that would be raised
if we passed a Null value to the Replace function, which expects a string.

--
Brendan Reynolds (MVP)


Omar Menjivar said:
Brendan, Thanks! This works great!

Another question, is there any way to count these characters "Chr$(10)" in
the record? For example, if a record holds 5 values and each value is
followed by a carraige return:
1154 Chr$(10) 1258 Chr$(10) 1350 Chr$(10) 4587 Chr$(10) 5500
In this case theres 4 Chr$(10) characters.
How can i count how many "Chr$(10)" characters or Values are in the that
record?
Click to expand...
Click to expand...
 
Back
Top