Cleansing Access Data

[brian_adkins]

I know there is a function in Excel to Clean data to
remove tabs/returns/unprintable char but I can't find the
same functionality in Access 2000. Does anyone have any
ideas. I have done extensive searches on net and had no
luck. The problem exists when I export the data as .csv
the records are becoming variable length and trucn recs.

 

[John Nurick]

Hi Brian,

No, there isn't a built-in function. In Access, I'd do this by writing a
custom VBA function to clean a string, and call this function in an
update query (to clean a table) or select query (if I wanted to export
cleaned data without changing the records in the table).

Alternatively, you could export the data to Excel and clean it there
before exporting again.

By the way, when exporting from Access to a textfile, CSV is the format
best able to handle linebreaks and other non-printing characters in the
data.

I know there is a function in Excel to Clean data to
remove tabs/returns/unprintable char but I can't find the
same functionality in Access 2000. Does anyone have any
ideas. I have done extensive searches on net and had no
luck. The problem exists when I export the data as .csv
the records are becoming variable length and trucn recs.

John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.