Filter results based on formatting

[dnnr]

I have a field of user inputed data. The data are dates. The standardized way we'd like dates entered is DMMDDYYYY. The first D is inconsequential, it just needs to be present, whereas M means month, the D means day, and the Y means year.

I would like to filter my results to ONLY see the data that has been inputed correctly. Can you filter data in a query based on formatting, and if yes, how so?

(The title is somewhat misleading because I don't want to filter the results necessarily, I just want the incorrectly entered data to not even show up period)

 

[Brianwarnock]

How would you know if 1101 was the 1st of November or 11th of January.
All you can do is check that the last 4 are between say 1940 and 2010 or whatever suits, and that the MM is between 1 and 12, you can do this using Right and Mid functions.

Brian

 

[dnnr]

1st of November - D11012007
11th of January - D01112007

 

[Brianwarnock]

The point I was trying to make is that you cannot be 100% sure that the date is entered correctly, what you can check is limited to ranges as I stated.

Brian

 

[Rabbie]

You can check a date has been entered in the correct format but you cannot tell just by looking if the correct date has been input. In that case you need to validate the date against the range of possible values in that context.

ie
1st of November - D11012007
11th of January - D01112007

are both correctly formatted date but depending on the context you may not be allowed to enter a date in the future.

 

[neileg]

Why don't you store dates as dates? You can display them in the desired format, if that's what you want. Dates stored as dates are automatically validated plus you can use them as dates in calculations. Using a perverse format for storage simply increases the chances of getting it wrong.