Wild Cards with Dates

[ChuckW]

Hi,

I am extracting date values out of a text field that has
both dates and other non dates such as Insurance company
names. As part of my query I used the statement where
CustomField1 like "01/*" or CustomField1 like "02/*"
After extracting the dates, I then used a CDate function
on the text field to convert it into a Date/Time value
field. This ran fine for a while until someone typed an
incorrect value into this field. They inputted 03/57/57
which nulified the entire query and gave me a data type
mismatch error. This is a lot of extra work but I went
in and typed customfield1 like "01/0*/*" or customfield1
like "01/2*/*" or customfield like "01/30/*" or
customfield like "01/31/*" for every month. This would
prevent an invalid entry from stopping my query. Does
anyone have a better way of doing this?

Thanks,

Chuck

 

[Graham Mandeno]

Hi Chuck

How about IsDate([customfield]) ?

 

[Dale Fye]

How about using something like:

WHERE ISDATE(LEFT([yourField], 8))

This should work as long as the dates are all entered with two digit
year, month, and day. Otherwise, you could test and use everything to
the left of the first space.

--
HTH

Dale Fye


Hi,

I am extracting date values out of a text field that has
both dates and other non dates such as Insurance company
names. As part of my query I used the statement where
CustomField1 like "01/*" or CustomField1 like "02/*"
After extracting the dates, I then used a CDate function
on the text field to convert it into a Date/Time value
field. This ran fine for a while until someone typed an
incorrect value into this field. They inputted 03/57/57
which nulified the entire query and gave me a data type
mismatch error. This is a lot of extra work but I went
in and typed customfield1 like "01/0*/*" or customfield1
like "01/2*/*" or customfield like "01/30/*" or
customfield like "01/31/*" for every month. This would
prevent an invalid entry from stopping my query. Does
anyone have a better way of doing this?

Thanks,

Chuck