Date Validation Problem

Topic

New Reply

This applies to both 97 SR-2 and 2000.
I am outside US, so short date format is dd/mm/yy.
When 31/02/01 is entered into a date field, Access takes it as 1 Feb 1931. Short of writing my own date validation routine has anyone any suggestions.

Thanks

Mark Wheeler

Reply | Quote

Replies

Hi Mark,
I don’t know of any simple way of preventing Access from being ‘helpful’ in this way, except perhaps forcing your users to enter 4-digit years. However, if you want any kind of meaningful warning message when they enter an invalid date, I think you’ll have to go with your own validation routine.
Hope that helps.

Reply | Quote

I’d not seen the date was invalid. (Cringe , cringe, find hole to go and hide in)

Reply | Quote

Just to agree that the way access does this is a big problem. I had a query about this on the old forum that Douglas and Legare contributed to and after much hair pulling I gave up and just lived with a basic year validation to pick up the worst errors.

Reply | Quote

Do you mean your database will be used in various countries? If it is just in one country then have a look at the settings of the PC’s it runs on.
I use both English and German versions of Access in Switzerland (day/month/year) and have not experienced this ‘problem’ with Access 2, 95, 97 or 2000 (XP – not got it yet)

Reply | Quote

Hi Andy,
I’ve got Access 2000 on my PC, regional settings showing ShortDate as dd/mm/yy, and it does exactly what is described. It works perfectly if you enter a valid date, but if you enter an invalid one like 31/02/01, Access does try to convert it to a valid one. Does this not happen on yours?

Reply | Quote

Yes, it sure would be nice to be able to turn off Access’s “second guessing”! IMHO it increases data-entry errors rather than decreasing them.

In any case, are all dates valid in your database or can you put in a validation rule to check for sensible years? That’s simpler than parsing dates yourself.

Reply | Quote

Hi Douglas,
It’s not actually my database that has the problem. I am in the probably enviable position of mostly building databases for my own use and in those rare cases where I have to let others use them, they’re generally importing data from elsewhere so I don’t have to worry about user input at least!

Reply | Quote

Since 31 Feb 2001 is not a valid date, Access looked for a combination that is valid and concluded that the user entered yy/mm/dd. I think that the only way to prevent that is to write code to do your own validation.

Reply | Quote

I hate the auto-correct-date functionality that Access forces upon you. I wrote the following simple function to override it.

Public Function CheckDate(frm As Form) As Boolean
On Error GoTo CheckDate_Err
‘Call this function from a date field’s BeforeUpdate event like this:- ‘
‘Cancel = CheckDate(Me)
‘
‘NB: Set the date field’s input mask to 99/99/0000;0;_

Dim strDate1 As String
Dim strDate2 As String
Dim TxtBox As TextBox

Set TxtBox = frm.ActiveControl

strDate1 = TxtBox.Text
strDate2 = Format(TxtBox.Text, “dd/mm/yyyy”)

If StrComp(strDate1, strDate2) 0 Then
CheckDate = True
MsgBox “Please enter a valid date”, vbCritical, “Validation Error!”
Else
CheckDate = False
End If

CheckDate_Exit:
Exit Function
CheckDate_Err:
ErrorLog conModuleError, “CheckDate”, Err.Number, Err.Description
Resume CheckDate_Exit

End Function

HTH

Dave.

Reply | Quote