Access 2003 Date Input problem

[Jim Franklin]

Hi,

I have an Access form with bound controls to a couple of Date fields.

The controls have a Format of "dd/mm/yy" and the input mask is
"00/00/00;0;_"

The problem I am experiencing is that if I enter an invalid date, e.g.
31/11/08 (i.e. 31 Nov 2008!) Access ignores the format and converts this
automatically to 08/11/31 (08 Nov 1931) rather than highlighting the error.

I have never experienced this before, although I normally use the dd/mm/yyyy
format (not possible in this case.)

Can anyone tell me why Access is doing this and what I can do to prevent it?

Many thanks,
Jim

 

[Ken Snell [MVP]]

You likely will need to use code in the BeforeUpdate event for the control
to do your own validation before ACCESS changes it. Code might be like this:

Private Sub ControlName_BeforeUpdate(Cancel As Integer)
Dim strUSDateFormat As String
strUSDateFormat = Mid(Me.ControlName.Value, _
InStr(Me.ControlName.Value, "/") + 1, InStrRev(Me.ControlName.Value,
"/") - _
InStr(Me.ControlName.Value, "/") - 1) & "/" & _
Left(strdate, InStr(Me.ControlName.Value, "/") - 1) & "/" & _
Right(Me.ControlName.Value, Len(Me.ControlName.Value) - _
InStrRev(Me.ControlName.Value, "/"))
If IsDate(strUSDateFormat) = False Then
Cancel = True
MsgBox "You've entered an invalid date!", vbOK, "Invalid Date"
End If
End Sub

 

[Jim Franklin]

Thanks Ken,

Does anyone know why this is happening and is it something others have
experienced? I don't recall this happening before when I have used a
dd/mm/yy format.

Cheers,
Jim

 

[Ken Snell [MVP]]

I don't think this is new behavior. If ACCESS cannot "translate" a date to a
valid date, it normally reverts to using US format for dates and then does
its thing.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/