mistux Site Admin
Joined: 25 Jun 2004 Posts: 1042 Location: South Bend, Indiana USA
|
Posted: Wed Sep 26, 2007 10:59 am Post subject: Get proper date format |
|
|
Now, if you want to just enter month/day or whatever, here is a function I use frequently, developed in A97. I use this in the after update event of a text box; =fGetDate([tbxtest]), allowing me to enter enter a single number, which the function interprets as a day of the current month/year, or a month/year which the function interprets as the current year. If you'reambitious, you can fiddle around with text values and use it in a before update event, but after update works for me.
Code: |
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)
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
txtDate = DateValue(strDate) 'this will raise an error 2115 if it doesn 't work
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
|
|
|