Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Date question

Status
Not open for further replies.

MrM121

Programmer
Aug 21, 2003
83
GB
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
 
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 and
Duane
MS Access MVP
Find out how to get great answers faq219-2884.
 
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
 
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.
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top