removing empty records, leading spaces

[Ken N]

I have inherited a database that was imported from an
excell spreadsheat. Many of the records are empty. MOst
of the first name fields have leading spaces. Often, when
I (manually) remove leading spaces, I get duplicate name
index problems. Is there an easy way (VBA?) to clean up
this file?

 

[Allen Browne]

Ken, the easiest way to import data is usually to import into a temp table
that has no validation rules. Then run a series of queries to clean up and
validate the data.

Example to clean up the FirstName field:
db.Execute "UPDATE MyTemp SET [FirstName] = Str2Null([FirstName]);"
where Str2Null() is a function that trims the spaces and converts
zero-length strings to Null.

You would also run some outer join queries to find unmatched foreign keys
(like Unmatched query wizard), group-by queries to find duplicates, etc.