Convert entry to non-standard date

[mmitchell]

I have a need to be able to enter "feb 06" into a text field on a form and get "Feb/2006" stored.

Also if they enter just "feb" then the current year be used, so if it was 2007 then "Feb/2007" would get stored.

I tried to use this code, in the fields after update, but it does not deal with the "feb" or text part.

Function fGetDate(txtDate As Access.TextBox) As Boolean

'Used to get proper date format in the after update event. Must be used with a text box

Dim strGetDateMessage As String
Dim strGetDateError As String
Dim dteDate As Date
Dim strDate As String

On Error GoTo Err_Proc

strDate = txtDate

If IsNumeric(strDate) Then 'if only numbers, then insert slash

    Select Case Len(strDate)
    
        Case 1, 2 'if numeric and only one or two, interpet as day
        
            'strDate = Month(Date) & "/" & strDate & "/" & Year(Date)
            strDate = Month(Date) & "/" & Year(Date)
        
        Case 3, 4 'at least two numbers, then interpret first two as month, after as day strDate = Mid(strDate, 1, 2) & "/" & Mid(strDate, 3) & "/" & Year(Date)
        
        Case Else
        
        strDate = Mid(strDate, 1, 2) & "/" & Mid(strDate, 3, 2) & "/" & Mid(strDate, 5)
    
    End Select

End If

'strDate = DateValue(strDate) 'cdate could also be used

'if the above works, then everything is fine otherwise error 13
    Debug.Print strDate
'txtDate = DateValue(strDate) 'this will raise an error 2115 if it doesn 't work
txtDateJunk = DateValue(strDate) 'this will raise an error 2115 if it doesn 't work
txtDate = strDate
    Debug.Print txtDate
fGetDate = True

Exit_Proc:

Exit Function

Err_Proc:

Select Case Err.Number

Case 13

strGetDateError = "Date entered improperly @" & _
"There are a number of formats you may use:" & vbCrLf & vbCrLf & _
"1. Numbers and slashes, eg, MM/DD/YY (or YYYY), M/D/YY" & vbCrLf & vbCrLf & _
"2. Name of a month (3 or more letters) day, year (in any order) with spaces or commas between" & vbCrLf & vbCrLf & _
" eg, 12 Apr 1999, Apr 2000 12, April 1, 99, etc" & vbCrLf & vbCrLf & _
"(for 1 & 2, if the year is left out, the current year will be assumed)" & vbCrLf & vbCrLf & _
"3. Numbers with no delimiters:" & vbCrLf & _
"* 1 or 2 numbers is day (current month & year added)" & vbCrLf & _
"* 3 or 4 numbers is month/day (current year added)" & vbCrLf & _
"* 5 to 8 numbers will be interpeted as month/day/year."

strGetDateMessage = MsgBox(strGetDateError, 0 + 64, "Improper Date Format")

txtDate = Null

fGetDate = False

GoTo Exit_Proc

End Select

End Function

 

[ByteMyzer]

How about this?:

Function fGetDate(txtDate As Access.TextBox) As Boolean

    Dim sDate As String

    sDate = txtDate.Value

    If IsDate(Left(sDate, 3) & " " & Format(Date, "yy")) = False Then
        MsgBox "Improper Date Format"
        Exit Function
    End If

    If Len(sDate) = 3 Then
        sDate = sDate & " 1 " & Format(Date, "yy")
    Else
        sDate = Left(sDate, 3) & " 1 " & Right(sDate, 2)
    End If

    txtDate.Value = Format(sDate, "mmm/yyyy")
    fGetDate = True

End Function