Now you know the reason that having this data stored with other data items
is a bad idea.
Using Regular expression to snag the date looks like your best choice. I'm
not up to that challenge.
You would need to find and extract data that matched the pattern
1 or 2 numbers, a separator character, 1 or 2 numbers, a separator
character, and 2 or 4 numbers.
You can try the following function (not fully tested). It looks for date
strings in several configurations in order of length.
-- It first looks for a 10 character date mm/dd/yyyy and returns the first
instance of that
-- If no 10 character date is found it looks for a 9 character date
m/dd/yyyy or mm/d/yyyy and returns the first instance of that
-- (8) Then m/d/yyyy or mm/dd/yy
-- (7) Then m/d/yy or mm/d/yy or m/dd/yy
-- (6) Then m/d/yy
Function fExtractDatePattern(strIn)
Dim i As Long
Dim strDate As String
Const Cs1 As String = "#[-/.]"
Const Cs2 As String = "##[-/.]"
Const Cs2y As String = "##"
Const Cs4y As String = "####"
'Warning this works for US date patterns
'It may or may not work for other patterns
If Len(strIn & "") > 5 Then
For i = 1 To Len(strIn) - 9
If Mid(strIn, i, 10) Like Cs2 & Cs2 & Cs4y And _
IsDate(Mid(strIn, i, 10)) Then 'mm/dd/yyyy
strDate = Mid(strIn, i, 10)
Exit For
End If
Next i
If Len(strDate) = 0 Then
For i = 1 To Len(strIn) - 8
If (Mid(strIn, i, 9) Like Cs1 & Cs2 & Cs4y Or _
Mid(strIn, i, 9) Like Cs2 & Cs1 & Cs4y) And _
IsDate(Mid(strIn, i, 9)) Then 'm/dd/yy or mm/d/yyyy
strDate = Mid(strIn, i, 9)
Exit For
End If
Next i
End If
If Len(strDate) = 0 Then
For i = 1 To Len(strIn) - 7
If (Mid(strIn, i, 8) Like Cs2 & Cs2 & Cs2y Or _
Mid(strIn, i, 8) Like Cs1 & Cs1 & Cs4y) And_
IsDate(Mid(strIn, i, 8)) Then 'mm/dd/yy or m/d/yyyy
strDate = Mid(strIn, i, 8)
Exit For
End If
Next i
End If
If Len(strDate) = 0 Then
For i = 1 To Len(strIn) - 6
If (Mid(strIn, i, 7) Like Cs1 & Cs2 & Cs4y Or _
Mid(strIn, i, 7) Like Cs2 & Cs1 & Cs4y) And_
IsDate(Mid(strIn, i, 7)) Then 'm/dd/yyyy ormm/d/yyyy
strDate = Mid(strIn, i, 7)
Exit For
End If
Next i
End If
If Len(strDate) = 0 Then
For i = 1 To Len(strIn) - 5
If Mid(strIn, i, 7) Like Cs1 & Cs1 & Cs2y And _
IsDate(Mid(strIn, i, 6)) Then 'm/d/yy
strDate = Mid(strIn, i, 6)
Exit For
End If
Next i
End If
If Len(strDate) > 0 Then 'found a date
fExtractDatePattern = strDate
End If
End If
End Function
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
.
It works nice until it encounters a character right next to the date.
ie., 09/22/06-good
the - or letter next to the last number messes it up
First question is there MORE than one date in the field? Assuming only one
date the function below should work to give you the date as a string.
Function fExtractDate(strIn)
Dim s As Variant
Dim i As Long
If Len(strIn & "") = 0 Then Exit Function
s = Split(strIn, " ")
For i = LBound(s) To UBound(s)
If IsDate(s(i)) Then
fExtractDate = s(i)
' Or if you wanted the date back as a datetime field
' fExtractDate = CDate(s(i) )
Exit Function
End If
Next i
In your query you would call it as
Field: TheDate: fExtractDate([Name of the field])
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
.- Hide quoted text -
- Show quoted text -