Date question

[MrM121]

Here's an interesting question.

I have set up a date field (ordinary, bog standard date) and used the format dd/mm/yy to store it.

Here is the problem. A normal date, say 12 January, 2004, would be entered as such 12/12/04 - fair enough. However, lets say when the user is typing in the date, they make a mistake, and instead type 12/21/04. For some reason, Access rearranges the date into the following, 21/12/04, without giving an error message. So it now reads 21st January 2004.

I tried the same with an ordinary Short Date as the format, and the same occurs. I am using Access 2003.

Does anyone know why and, better still, how to counteract it?

Thank you,

Nick

 

[dhookom]

I don't know how Access would ever interpret 12/12/04 as a date in January. I assume you mean December.

Keep in mind that date values are always stored exactly the same. It makes no difference how you format the date for display. The Regional Settings in the Control Panel determine how Short Date is displayed.

If you enter a date that doesn't fit your settings, Access will attempt to fix it for you.

There is additional information at

http://www.mvps.org/access/datetime/index.html

and

http://members.iinet.net.au/~allenbrowne/ser-36.html.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[MrM121]

Thank you,

I still don't really know how to correct the problem, without creating a custom control/ validation routine.

I don't know what I was thinking about with January instead of December - my bad.

Nick

 

[dhookom]

THis is just a WAG since my system is set for the American style date but you might try something like:
Private Sub txtDate_BeforeUpdate(Cancel As Integer)
If Val(Mid(Me.txtDate, InStr(Me.txtDate, "/") + 1)) > 12 Then
MsgBox "Bad date"
End If

End Sub


Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[MrM121]

Cheers for that,

I'll give it a crack and see what I come up with. That code seems fine, but I was just hoping that Access wouldn't try and be so 'clever'.

Nick