How to "Scrub" data in Access Database

[Dennis]

Using Access 2003

Have imported about 12,000 records from Excel.

All queries and reports work except .....

One record which has a dollar amount of 230.48, when searched by "Find"
or by query (Report)it is not located.

The numbers, above and below the 230.48, are located and "reported."

To no avail, I attempted a database rebuild.

Finally, I just deleted the number 230.48 and re-entered it - then it
worked just find using both the Find and query methods.

In Excel, there is a Trim(Clean(A1)) set of functions which will strip
all control characters (not visible) out of cells.
(I believe that there was a control-character in the 230.48 field of
that data record)

Is there and analogous (field control-character-stripping) function for
Access fields?

Dennis

 

[John Nurick]

Hi Dennis,

What value appeared in the record where it should have been 230.48? Was
an Import Errors table generated when you did the import?

Assuming the problem is a control character in the worksheet cell, if
you know how to clean the data in Excel it's usually simpler to do it
there and import clean data into Access.