Smarter Date/Number Field Validation for Bad Excel Import

[Dugantrain]

Hi, all, just another day in Excel Hell. What I have is an Excel Spreadsheet which has a myriad of different data types in its numerical and date fields. The procedure that I came up with which works (to this point, anyway) is to write automated queries which, when this data is imported, looks at the numerical/date fields, lumps the bad data into another "Import_Comments" field, and then deletes the bad data out of the original fields. The validation I used is:

Not between "0*" and "9*"

However, some times a field can pass this validation and still not import. For example, a date field's data may be something weird like:

6/3/200+J803

So, I need a smarter validation, something like:

Not like ##/##/####

for the date fields. But the query doesn't seem to care much for that syntax. I also need the numerical fields to ensure that ONLY numbers are contained within their fields with no text.

 

[Calvin]

Look up the IsDate() and IsNumeric() functions in the help file, I think they will be your solution for validating the data.

 

[Dugantrain]

Well, it wasn't in the 2002 Help Files (why are there so many functions omitted from the Access help files?), but I dug around on this site to see how it works and it accomplishes this task perfectly. Thanks so much.

 

[Pat Hartman]

Those functions are in the help file. You need to open help from a VBA window to get help on most functions.