Check Date Format in Query

[mcktigger]

Hi All

I have a linked foxpro table in my db I need to query. In the Foxpro system there is a text field where users put in a date so no validation to ensure that the user has put the date in the correct format (I can't change the foxpro system). I therefore need to run an exceptions query to pick up if a user has anything in this field other than a date in format dd/mm/yy e.g a space at the start, missing zeros etc

Is it possible in the query criteria to put something like Date <> dd/mm/yy format.

Thanks for any help offered.

 

[Guus2005]

You can never tell if 02/01/11 is Jan 2, 2011 or Feb 1, 2011.

Using the IsDate function the system checks if it a valid date or it can be converted to one.

If everyone enters the date correctly in a dd/mm/yy format, 12/31/11 will be converted to 31/12/11 using the CVDate() function. It doesn't matter if you leave out any preceding zero's 1/1/11 and 01/01/11 is the same.
If someone enters 1-1-11 instead of 1/1/11, CVDate() will convert it into a valid date format.

HTH

 

[mcktigger]

Hi

Excellent thanks for your help.