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 Vinson]

(I believe that there was a control-character in the 230.48 field of
that data record)

If it's a Currency field, I very much doubt it. Unlike Excel cells,
Access fields have "strong datatypes" - and Currency fields can
contain only numbers, no other characters. What it might be is a
hidden fraction of a cent: Currency fields have *four* decimal places,
no more, no fewer. You might have the field formatted to show only two
but if the actual table value is 230.4805 you'ld get this effect.

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

No.

John W. Vinson[MVP]

 

[Tony Toews]

If it's a Currency field, I very much doubt it. Unlike Excel cells,
Access fields have "strong datatypes" - and Currency fields can
contain only numbers, no other characters. What it might be is a
hidden fraction of a cent: Currency fields have *four* decimal places,
no more, no fewer. You might have the field formatted to show only two
but if the actual table value is 230.4805 you'ld get this effect.

Good catch. I wouldn't have thought of that.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm