Access 2003 Date Input problem

  • Thread starter Thread starter Jim Franklin
  • Start date Start date
J

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
 
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
 
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
 
Back
Top