Date Error in Access

Question

I defined a date field in an Access table as a short date.  When I key dates into it, I noticed that Access allows the user to key in an erroneous date. For example, when I key in 13/12/2012, Access converts this to 12/13/2012.  Is there an autocorrect setting or something else I'm missing? We are running Access on a Windows XP machine.

Thanks for the help!!

Ken K. - 2191  

.

Answer 1

i will add on to John's answer that Acccess goes to great lengths to interpret a wide variety of dates that might be entered by users, and I find that very convenient, both as a user and as a developer.

It would be possible to use an input mask to restrict the sorts of date entries that users can make, but it still wouldn't help you in keeping a user from entering "13/12/2012".  And you could use code in the text box's BeforeUpdate event to examine the actual text the user entered and possibly reject and entry such as "13/12/2012", but:

1. That won't help you determine whether an entry "7/9/12" means July 9th or September 7th, and
2. If the user enters "13/12/2012", don't you really know what they intended, anyway?  Why not just let Access correct it?

Answer 2

That is a perfectly correct and normal date in most of the world. The United States convention of month/day/year is used in some other places, but most nations prefer the more logical day/month/year. Access recognizes both; since its programmers were from the US, they made month/day/year the default, and as a result 7/9/12 will be recognized as the 9th of July; but if a date entry doesn't make sense as month/day/year the program will try the other alternative.

Note that a date is actually stored in the table without any particular format; instead, it's stored as a double float count of days and fracions of a day since midnight, December 30, 1899. The format is applied when that number is displayed or entered.

Answer 3

I like the telepathic keyboard interface (TKI for short) idea!!  You Microsoft MVP's are the best.  You always manage to teach me something helpful!!  I have never heard of a graphical date picker, but am looking forward to trying it.  I have seen it in PDF forms but didn't know how to set it up in Acrobat.

Thanks again to all of you!!

Ken K.

Answer 4

I'd just put a graphical datepicker on the form so the user can select a date from a familiar calendar. You could - using unbound controls and some rather tricky VBA code - detect SOME transposed dates; but as Dirk says, until we get the telepathic keyboard interface there is no way to determine whether 7/9/12 means July 9 or September 7.

Answer 5

Thanks John.  I didn't know that!!  Never too old to learn something new.  Do you have a suggestion on how to keep someone from transposing the date in my example (13/12/2012) i.e. use some other software?

Have a great day!!

Ken K. - 2191