Invalid dates being accepted!

[tpickles1984]

Using Access 97, I made a table which contained a short date format. I tested that it was rejecting invalid dates by entering 35/07/02 in to the field (35th July, 2002). However, instead of refusing it, it flipped it around to give 02/07/2035! This could be a bit distastrous when being used, is there a way to stop it? I currently have no input masks or validation rules applied. Any suggestions?

 

[ColinEssex]

Check the Regional Settings in Windows and make sure they are relevant to wherever you are.

Edit - I see you're in the Uk so make sure it is set to English (British) not English (USA) as they have "backwards" dates there compared to the UK.

Col

 

[Pat Hartman]

I think Access is being too nice to you. Microsoft should have based the edit rules on either the specific field format or the default system format. This would have solved a lot of problems. Instead, they try to make sense out of the date and if there is a way that they can interpret the string as a valid date by shifting around the components, they do it.

You're stuck with this behaviour unless you want to do your own edits in the BeforeUpdate event of the date field. You know that you intend the field to be interpreted as mm/dd/yy or dd/mm/yy but NOT yy/dd/mm.