Allow user to separate dates with period in MS Access

I am working with an Access database where I have a form that co开发者_C百科ntains several date entry fields. I have a new user that is used to using a period as a delimiter for dates so instead of "6/22/11" or "6-22-11", they enter "6.22.11". I would like to continue to allow this type of entry, but Access converts "6.22.11" to a time instead of a date. I have tried setting the format on the text box to "Short Date" with no help. I have also tried adding code to the "Lost Focus" event but that is to late since Access has already done the conversion. The "Before Update" event fires before the conversion but will not allow me to change the text in the textbox. Any ideas on how I can allow all three forms of date entry?

your above example

Private Sub Texto0_KeyPress(KeyAscii As Integer)
    If Chr(KeyAscii) = "." Then
        KeyAscii = Asc("/")
    End If
End Sub

works for me.

Another aproximation is play with the BeforeUpdate and AfterUpdate events. In BeforeUpdate you cannot modify de content of the control, but you can set a flag (a variable defined at module/form level) in the AfterUpdate event and change the content: it will trigger again the BeforeUpdate, but in this case, because is flagged you sould ignore it and unflag.

http://office.microsoft.com/en-us/access-help/control-data-entry-formats-with-input-masks-HA010096452.aspx

Input Mask.

I wrote the following function for a user who was used to entering 6 and 8 digit dates in input masks by just typing a string of numbers with no delimiter. You should be able to modify it for your purposes:

'---------------------------------------------------------------------------
' Purpose   : Enables entry of 8-digit dates with no delimiters: 12312008
' Usage     : Set OnChange: =DateCtlChange([Form].[ActiveControl])
'  8/ 6/09  : Allow entry of 6-digit dates with no delimiters
'               (year 2019 and 2020 must still be entered as 8-digit dates)
'---------------------------------------------------------------------------
Function DateCtlChange(DateCtl As TextBox)
Dim s As String, NewS As String

    On Error GoTo Err_DateCtlChange

    s = DateCtl.Text
    Select Case Len(s)
    Case 6
        If s Like "######" Then
            If Right(s, 2) <> "19" And Right(s, 2) <> "20" Then
                NewS = Left(s, 2) & "/" & Mid(s, 3, 2) & "/" & Mid(s, 5, 2)
            End If
        End If
    Case 8
        If s Like "########" Then
            NewS = Left(s, 2) & "/" & Mid(s, 3, 2) & "/" & Mid(s, 5, 4)
        End If
    End Select
    If IsDate(NewS) Then
        DateCtl.Text = NewS
        DateCtl.SelStart = Len(DateCtl.Text)
    End If


Exit_DateCtlChange:
    Exit Function
Err_DateCtlChange:
    Select Case Err.Number
    'Error 2101 is raised when we try to set the text to a date 
    '    that fails the date control's validation
    Case 2101    'The setting you entered isn't valid for this property.
        'Log error but don't show user
    Case Else
        'Add your custom error logging here
    End Select
    Resume Exit_DateCtlChange
End Function

Access uses the system date and time format to determine how to translate a value. Another option - that would affect every program on this user's computer - is this: http://office.microsoft.com/en-us/access-help/change-the-default-date-time-number-or-measurement-format-HA010351415.aspx?CTT=1#BM2

Alternatively you can use spaces instead of slashes in dates in Access. Thus the user can use the left hand on the space bar and use the right hand on the numeric keyboard. I feel this is much easier to use than either the slash or the hyphen.