use IsDate() function:
Code:If IsDate([yourTextField]) 'valid date date Else 'not valid date End If
I will elaborate my problem:
In my text field I type:"Feast on 08-08-19"
Now, I want to check whether the date that is typed in the message is in Date format.
str1="Feast on might be on 08-08-19"
? IsDate(Mid(str1,InstrRev(str1," ")+1))
True
'arnelgp
'find date inside a string
'
'returns string of dates if found
Public Function DatesInText(strText As String) As String
Dim arrPattern(1 To 36) As String
Dim oRE, oMatches, oMatch
Dim i As Integer
Dim strDates As String
Dim collDates As Collection
arrPattern(1) = "(0[1-9]|1[012])[- /.](0[1-9]|[12][0-9]|3[01])[- /.](19|20)[0-9]{2}" 'mm/dd/yyyy or mm-dd-yyyy
arrPattern(2) = "(0[1-9]|1[012])[- /.](0[1-9]|[12][0-9]|3[01])[- /.][0-9]{2}" 'mm/dd/yy or mm-dd-yy
arrPattern(3) = "([1-9]|1[012])[- /.](0[1-9]|[12][0-9]|3[01])[- /.](19|20)[0-9]{2}" 'm/dd/yyyy or m-dd-yyyy
arrPattern(4) = "([1-9]|1[012])[- /.](0[1-9]|[12][0-9]|3[01])[- /.][0-9]{2}" 'm/dd/yy or m-dd-yy
arrPattern(5) = "([1-9]|1[012])[- /.]([1-9]|[12][0-9]|3[01])[- /.](19|20)[0-9]{2}" 'm/d/yyyy or m-d-yyyy
arrPattern(6) = "([1-9]|1[012])[- /.]([1-9]|[12][0-9]|3[01])[- /.][0-9]{2}" 'm/d/yy or m-d-yy
arrPattern(7) = "(0[1-9]|[12][0-9]|3[01])[- /.](0[1-9]|1[012])[- /.](19|20)[0-9]{2}" 'dd/mm/yyyy or dd-mm-yyyy
arrPattern(8) = "(0[1-9]|[12][0-9]|3[01])[- /.](0[1-9]|1[012])[- /.][0-9]{2}" 'dd/mm/yy or dd-mm-yy
arrPattern(9) = "(0[1-9]|[12][0-9]|3[01])[- /.]([1-9]|1[012])[- /.](19|20)[0-9]{2}" 'dd/m/yyyy or dd-m-yyyy
arrPattern(10) = "(0[1-9]|[12][0-9]|3[01])[- /.]([1-9]|1[012])[- /.][0-9]{2}" 'dd/m/yy or dd-m-yy
arrPattern(11) = "([1-9]|[12][0-9]|3[01])[- /.]([1-9]|1[012])[- /.](19|20)[0-9]{2}" 'm/d/yyyy or m-d-yyyy
arrPattern(12) = "([1-9]|[12][0-9]|3[01])[- /.]([1-9]|1[012])[- /.][0-9]{2}" 'd/m/yy or d-m-yy
arrPattern(13) = "(19|20)[0-9]{2}[- /.](0[1-9]|1[012])[- /.](0[1-9]|[12][0-9]|3[01])" 'yyyy/mm/dd or yyyy-mm-dd
arrPattern(14) = "[0-9]{2}[- /.](0[1-9]|1[012])[- /.](0[1-9]|[12][0-9]|3[01])" 'yy/mm/dd or yy-mm-dd
arrPattern(15) = "(19|20)[0-9]{2}[- /.]([1-9]|1[012])[- /.](0[1-9]|[12][0-9]|3[01])" 'yyyy/m/dd or yyyy-m-dd
arrPattern(16) = "[0-9]{2}[- /.]([1-9]|1[012])[- /.](0[1-9]|[12][0-9]|3[01])" 'yy/m/dd or yy-m-dd
arrPattern(17) = "(19|20)[0-9]{2}[- /.]([1-9]|1[012])[- /.]([1-9]|[12][0-9]|3[01])" 'yyyy/m/d or yyyy-m-d
arrPattern(18) = "[0-9]{2}[- /.]([1-9]|1[012])[- /.]([1-9]|[12][0-9]|3[01])" 'yy/m/d or yy-m-d
''' jan, feb, etc...
arrPattern(19) = "(jan|feb|mar|apr|may|jun|jul|aug|sep|oct|nov|dec)[- /.](0[1-9]|[12][0-9]|3[01])[- /.](19|20)[0-9]{2}" 'mm/dd/yyyy or mm-dd-yyyy
arrPattern(20) = "(jan|feb|mar|apr|may|jun|jul|aug|sep|oct|nov|dec)[- /.](0[1-9]|[12][0-9]|3[01])[- /.][0-9]{2}" 'mm/dd/yy or mm-dd-yy
arrPattern(21) = "(jan|feb|mar|apr|may|jun|jul|aug|sep|oct|nov|dec)[- /.](0[1-9]|[12][0-9]|3[01])[- /.](19|20)[0-9]{2}" 'm/dd/yyyy or m-dd-yyyy
arrPattern(22) = "(jan|feb|mar|apr|may|jun|jul|aug|sep|oct|nov|dec)[- /.](0[1-9]|[12][0-9]|3[01])[- /.][0-9]{2}" 'm/dd/yy or m-dd-yy
arrPattern(23) = "(jan|feb|mar|apr|may|jun|jul|aug|sep|oct|nov|dec)[- /.]([1-9]|[12][0-9]|3[01])[- /.](19|20)[0-9]{2}" 'm/d/yyyy or m-d-yyyy
arrPattern(24) = "(jan|feb|mar|apr|may|jun|jul|aug|sep|oct|nov|dec)[- /.]([1-9]|[12][0-9]|3[01])[- /.][0-9]{2}" 'm/d/yy or m-d-yy
arrPattern(25) = "(0[1-9]|[12][0-9]|3[01])[- /.](jan|feb|mar|apr|may|jun|jul|aug|sep|oct|nov|dec)[- /.](19|20)[0-9]{2}" 'dd/mm/yyyy or dd-mm-yyyy
arrPattern(26) = "(0[1-9]|[12][0-9]|3[01])[- /.](jan|feb|mar|apr|may|jun|jul|aug|sep|oct|nov|dec)[- /.][0-9]{2}" 'dd/mm/yy or dd-mm-yy
arrPattern(27) = "(0[1-9]|[12][0-9]|3[01])[- /.](jan|feb|mar|apr|may|jun|jul|aug|sep|oct|nov|dec)[- /.](19|20)[0-9]{2}" 'dd/m/yyyy or dd-m-yyyy
arrPattern(28) = "(0[1-9]|[12][0-9]|3[01])[- /.](jan|feb|mar|apr|may|jun|jul|aug|sep|oct|nov|dec)[- /.][0-9]{2}" 'dd/m/yy or dd-m-yy
arrPattern(29) = "([1-9]|[12][0-9]|3[01])[- /.](jan|feb|mar|apr|may|jun|jul|aug|sep|oct|nov|dec)[- /.](19|20)[0-9]{2}" 'm/d/yyyy or m-d-yyyy
arrPattern(30) = "([1-9]|[12][0-9]|3[01])[- /.](jan|feb|mar|apr|may|jun|jul|aug|sep|oct|nov|dec)[- /.][0-9]{2}" 'd/m/yy or d-m-yy
arrPattern(31) = "(19|20)[0-9]{2}[- /.](0[1-9]|1[012])[- /.](jan|feb|mar|apr|may|jun|jul|aug|sep|oct|nov|dec)" 'yyyy/mm/dd or yyyy-mm-dd
arrPattern(32) = "[0-9]{2}[- /.](0[1-9]|1[012])[- /.](jan|feb|mar|apr|may|jun|jul|aug|sep|oct|nov|dec)" 'yy/mm/dd or yy-mm-dd
arrPattern(33) = "(19|20)[0-9]{2}[- /.]([1-9]|1[012])[- /.](jan|feb|mar|apr|may|jun|jul|aug|sep|oct|nov|dec)" 'yyyy/m/dd or yyyy-m-dd
arrPattern(34) = "[0-9]{2}[- /.]([1-9]|1[012])[- /.](jan|feb|mar|apr|may|jun|jul|aug|sep|oct|nov|dec)" 'yy/m/dd or yy-m-dd
arrPattern(35) = "(19|20)[0-9]{2}[- /.]([1-9]|1[012])[- /.](jan|feb|mar|apr|may|jun|jul|aug|sep|oct|nov|dec)" 'yyyy/m/d or yyyy-m-d
arrPattern(36) = "[0-9]{2}[- /.]([1-9]|1[012])[- /.](jan|feb|mar|apr|may|jun|jul|aug|sep|oct|nov|dec)" 'yy/m/d or yy-m-d
Set oRE = CreateObject("VBScript.RegExp")
Set collDates = New Collection
With oRE
.Global = True
.IgnoreCase = True
On Error Resume Next
For i = 1 To 36
.Pattern = arrPattern(i)
Set oMatches = .Execute(strText)
For Each oMatch In oMatches
collDates.Add oMatch.value, oMatch.value
Next
Next
End With
'remove dates not in origial text
For i = collDates.count To 1 Step -1
With oRE
.Pattern = "(^|[ \x0A\,\.])" & collDates(i) & "($|[ \x0A\,\.])"
Set oMatches = .Execute(strText)
If oMatches.count = 0 Then _
collDates.Remove collDates(i)
End With
Next
For i = 1 To collDates.count
strDates = strDates & collDates(i) & ","
Next
Set collDates = Nothing
Set oMatches = Nothing
Set oRE = Nothing
If strDates <> "" Then strDates = Left(strDates, Len(strDates) - 1)
DatesInText = strDates
End Function
Debug.Print DatesInText("Feast on 08-08-19")
result: 08-08-19
you can test the result with IsDate() function
if you have multiple dates in the string:
"Feast on 08-08-19 and end on 08-09-19"
it will return: 08-08-19,08-09-19
Better to have a control for the date ans check that.?
So have a textbox for Event and a textbox for the date. Check that has been completed and append to the value in the event control?
I see.
I don't know whether others have better approach but you will need a function to do that.
copy in new Module (VBA->Insert->Module):
Code:'arnelgp 'find date inside a string ' 'returns string of dates if found Public Function DatesInText(strText As String) As String Dim arrPattern(1 To 36) As String Dim oRE, oMatches, oMatch Dim i As Integer Dim strDates As String Dim collDates As Collection arrPattern(1) = "(0[1-9]|1[012])[- /.](0[1-9]|[12][0-9]|3[01])[- /.](19|20)[0-9]{2}" 'mm/dd/yyyy or mm-dd-yyyy arrPattern(2) = "(0[1-9]|1[012])[- /.](0[1-9]|[12][0-9]|3[01])[- /.][0-9]{2}" 'mm/dd/yy or mm-dd-yy arrPattern(3) = "([1-9]|1[012])[- /.](0[1-9]|[12][0-9]|3[01])[- /.](19|20)[0-9]{2}" 'm/dd/yyyy or m-dd-yyyy arrPattern(4) = "([1-9]|1[012])[- /.](0[1-9]|[12][0-9]|3[01])[- /.][0-9]{2}" 'm/dd/yy or m-dd-yy arrPattern(5) = "([1-9]|1[012])[- /.]([1-9]|[12][0-9]|3[01])[- /.](19|20)[0-9]{2}" 'm/d/yyyy or m-d-yyyy arrPattern(6) = "([1-9]|1[012])[- /.]([1-9]|[12][0-9]|3[01])[- /.][0-9]{2}" 'm/d/yy or m-d-yy arrPattern(7) = "(0[1-9]|[12][0-9]|3[01])[- /.](0[1-9]|1[012])[- /.](19|20)[0-9]{2}" 'dd/mm/yyyy or dd-mm-yyyy arrPattern(8) = "(0[1-9]|[12][0-9]|3[01])[- /.](0[1-9]|1[012])[- /.][0-9]{2}" 'dd/mm/yy or dd-mm-yy arrPattern(9) = "(0[1-9]|[12][0-9]|3[01])[- /.]([1-9]|1[012])[- /.](19|20)[0-9]{2}" 'dd/m/yyyy or dd-m-yyyy arrPattern(10) = "(0[1-9]|[12][0-9]|3[01])[- /.]([1-9]|1[012])[- /.][0-9]{2}" 'dd/m/yy or dd-m-yy arrPattern(11) = "([1-9]|[12][0-9]|3[01])[- /.]([1-9]|1[012])[- /.](19|20)[0-9]{2}" 'm/d/yyyy or m-d-yyyy arrPattern(12) = "([1-9]|[12][0-9]|3[01])[- /.]([1-9]|1[012])[- /.][0-9]{2}" 'd/m/yy or d-m-yy arrPattern(13) = "(19|20)[0-9]{2}[- /.](0[1-9]|1[012])[- /.](0[1-9]|[12][0-9]|3[01])" 'yyyy/mm/dd or yyyy-mm-dd arrPattern(14) = "[0-9]{2}[- /.](0[1-9]|1[012])[- /.](0[1-9]|[12][0-9]|3[01])" 'yy/mm/dd or yy-mm-dd arrPattern(15) = "(19|20)[0-9]{2}[- /.]([1-9]|1[012])[- /.](0[1-9]|[12][0-9]|3[01])" 'yyyy/m/dd or yyyy-m-dd arrPattern(16) = "[0-9]{2}[- /.]([1-9]|1[012])[- /.](0[1-9]|[12][0-9]|3[01])" 'yy/m/dd or yy-m-dd arrPattern(17) = "(19|20)[0-9]{2}[- /.]([1-9]|1[012])[- /.]([1-9]|[12][0-9]|3[01])" 'yyyy/m/d or yyyy-m-d arrPattern(18) = "[0-9]{2}[- /.]([1-9]|1[012])[- /.]([1-9]|[12][0-9]|3[01])" 'yy/m/d or yy-m-d ''' jan, feb, etc... arrPattern(19) = "(jan|feb|mar|apr|may|jun|jul|aug|sep|oct|nov|dec)[- /.](0[1-9]|[12][0-9]|3[01])[- /.](19|20)[0-9]{2}" 'mm/dd/yyyy or mm-dd-yyyy arrPattern(20) = "(jan|feb|mar|apr|may|jun|jul|aug|sep|oct|nov|dec)[- /.](0[1-9]|[12][0-9]|3[01])[- /.][0-9]{2}" 'mm/dd/yy or mm-dd-yy arrPattern(21) = "(jan|feb|mar|apr|may|jun|jul|aug|sep|oct|nov|dec)[- /.](0[1-9]|[12][0-9]|3[01])[- /.](19|20)[0-9]{2}" 'm/dd/yyyy or m-dd-yyyy arrPattern(22) = "(jan|feb|mar|apr|may|jun|jul|aug|sep|oct|nov|dec)[- /.](0[1-9]|[12][0-9]|3[01])[- /.][0-9]{2}" 'm/dd/yy or m-dd-yy arrPattern(23) = "(jan|feb|mar|apr|may|jun|jul|aug|sep|oct|nov|dec)[- /.]([1-9]|[12][0-9]|3[01])[- /.](19|20)[0-9]{2}" 'm/d/yyyy or m-d-yyyy arrPattern(24) = "(jan|feb|mar|apr|may|jun|jul|aug|sep|oct|nov|dec)[- /.]([1-9]|[12][0-9]|3[01])[- /.][0-9]{2}" 'm/d/yy or m-d-yy arrPattern(25) = "(0[1-9]|[12][0-9]|3[01])[- /.](jan|feb|mar|apr|may|jun|jul|aug|sep|oct|nov|dec)[- /.](19|20)[0-9]{2}" 'dd/mm/yyyy or dd-mm-yyyy arrPattern(26) = "(0[1-9]|[12][0-9]|3[01])[- /.](jan|feb|mar|apr|may|jun|jul|aug|sep|oct|nov|dec)[- /.][0-9]{2}" 'dd/mm/yy or dd-mm-yy arrPattern(27) = "(0[1-9]|[12][0-9]|3[01])[- /.](jan|feb|mar|apr|may|jun|jul|aug|sep|oct|nov|dec)[- /.](19|20)[0-9]{2}" 'dd/m/yyyy or dd-m-yyyy arrPattern(28) = "(0[1-9]|[12][0-9]|3[01])[- /.](jan|feb|mar|apr|may|jun|jul|aug|sep|oct|nov|dec)[- /.][0-9]{2}" 'dd/m/yy or dd-m-yy arrPattern(29) = "([1-9]|[12][0-9]|3[01])[- /.](jan|feb|mar|apr|may|jun|jul|aug|sep|oct|nov|dec)[- /.](19|20)[0-9]{2}" 'm/d/yyyy or m-d-yyyy arrPattern(30) = "([1-9]|[12][0-9]|3[01])[- /.](jan|feb|mar|apr|may|jun|jul|aug|sep|oct|nov|dec)[- /.][0-9]{2}" 'd/m/yy or d-m-yy arrPattern(31) = "(19|20)[0-9]{2}[- /.](0[1-9]|1[012])[- /.](jan|feb|mar|apr|may|jun|jul|aug|sep|oct|nov|dec)" 'yyyy/mm/dd or yyyy-mm-dd arrPattern(32) = "[0-9]{2}[- /.](0[1-9]|1[012])[- /.](jan|feb|mar|apr|may|jun|jul|aug|sep|oct|nov|dec)" 'yy/mm/dd or yy-mm-dd arrPattern(33) = "(19|20)[0-9]{2}[- /.]([1-9]|1[012])[- /.](jan|feb|mar|apr|may|jun|jul|aug|sep|oct|nov|dec)" 'yyyy/m/dd or yyyy-m-dd arrPattern(34) = "[0-9]{2}[- /.]([1-9]|1[012])[- /.](jan|feb|mar|apr|may|jun|jul|aug|sep|oct|nov|dec)" 'yy/m/dd or yy-m-dd arrPattern(35) = "(19|20)[0-9]{2}[- /.]([1-9]|1[012])[- /.](jan|feb|mar|apr|may|jun|jul|aug|sep|oct|nov|dec)" 'yyyy/m/d or yyyy-m-d arrPattern(36) = "[0-9]{2}[- /.]([1-9]|1[012])[- /.](jan|feb|mar|apr|may|jun|jul|aug|sep|oct|nov|dec)" 'yy/m/d or yy-m-d Set oRE = CreateObject("VBScript.RegExp") Set collDates = New Collection With oRE .Global = True .IgnoreCase = True On Error Resume Next For i = 1 To 36 .Pattern = arrPattern(i) Set oMatches = .Execute(strText) For Each oMatch In oMatches collDates.Add oMatch.value, oMatch.value Next Next End With 'remove dates not in origial text For i = collDates.count To 1 Step -1 With oRE .Pattern = "(^|[ \x0A\,\.])" & collDates(i) & "($|[ \x0A\,\.])" Set oMatches = .Execute(strText) If oMatches.count = 0 Then _ collDates.Remove collDates(i) End With Next For i = 1 To collDates.count strDates = strDates & collDates(i) & "," Next Set collDates = Nothing Set oMatches = Nothing Set oRE = Nothing If strDates <> "" Then strDates = Left(strDates, Len(strDates) - 1) DatesInText = strDates End Function
the function will return a string of dates that are on the text.
if you have a string like, eg:
Code:Debug.Print DatesInText("Feast on 08-08-19") result: 08-08-19 you can test the result with IsDate() function if you have multiple dates in the string: "Feast on 08-08-19 and end on 08-09-19" it will return: 08-08-19,08-09-19
I agree to your point. But it is not always possible to change the structure of the database. Hence we have to take this side path.I propose that we officially call this bad design habit "Attribute Mushing". Simple, concise and self explanatory.
I agree to your point. But it is not always possible to change the structure of the database. Hence we have to take this side path.
? datesintext("This is the report from 15 August 2019 for review")
15 August 2019|August 2019|15 August|August
datesintext("This is the report from 15 August 2019 for review",17)
datesintext("This is the report from 15 August 2019 for review","MonthYear")
Public Function DatesInText(strText As String) As String
'arnelgp
'find date inside a string
'returns string of dates if found
'https://www.access-programmers.co.uk/forums/showthread.php?t=306233
'20190808
'added full months and month year, month day, month only
'20190809 ss
Dim arrPattern(1 To 25) As String
Dim oRE, oMatches, oMatch
Dim i As Integer
Dim strDates As String
Dim collDates As Collection
'Separator Characters: slash (/) or dash (-) or dot (.) or space( )
arrPattern(1) = "(0?[1-9]|1[012])[- /.](0?[1-9]|[12][0-9]|3[01])[- /.](19|20)[0-9]{2}" 'mm/dd/yyyy or mm/d/yyyy or m/d/yyyy or m/dd/yyyy
arrPattern(2) = "(0?[1-9]|1[012])[- /.](0?[1-9]|[12][0-9]|3[01])[- /.][0-9]{2}" 'mm/dd/yy or mm/d/yy or m/d/yy or m/dd/yy
arrPattern(3) = "(0?[1-9]|[12][0-9]|3[01])[- /.](0?[1-9]|1[012])[- /.](19|20)[0-9]{2}" 'dd/mm/yyyy or dd/m/yyyy or d/m/yyyy or d/mm/yyyy
arrPattern(4) = "(0?[1-9]|[12][0-9]|3[01])[- /.](0?[1-9]|1[012])[- /.][0-9]{2}" 'dd/mm/yy or dd/m/yy or d/m/yy or d/mm/yy
arrPattern(5) = "(19|20)[0-9]{2}[- /.](0?[1-9]|[12][0-9]|3[01]){1,2}[- /.](0?[1-9]|1[012])" 'yyyy/dd/mm or yyyy/dd/m or yyyy/d/mm or yyyy/d/m
arrPattern(6) = "(19|20)[0-9]{2}[- /.](0?[1-9]|1[012])[- /.](0?[1-9]|[12][0-9]|3[01]){1,2}" 'yyyy/mm/dd or yyyy/mm/d or yyyy/m/dd or yyyy/m/d
arrPattern(7) = "[0-9]{2}[- /.](0?[1-9]|1[012])[- /.](0?[1-9]|[12][0-9]|3[01]){1,2}" 'yy/mm/dd or yy/mm/d or yy/m/dd or yy/m/d
arrPattern(8) = "(19|20)[0-9]{2}(0[1-9]|1[012])(0[1-9]|[12][0-9]|3[01]){1,2}" 'yyyymmdd must be 4 digit yr 2 digit mo 2 digit dt and no sep char
'LetterMonth Day Year(4 digits)
arrPattern(9) = "((?:Jan(?:uary)?|Feb(?:ruary)?|Mar(?:ch)?|Apr(?:il)?|May|Jun(?:e)?|Jul(?:y)?|Aug(?:ust)?|Sep(?:tember)?|Sept|Oct(?:ober)?|Nov(?:ember)?|Dec(?:ember)?))[\-\.\/\s](0?[1-9]|[12][0-9]|3[01])[- /.,]\s?(19|20)[0-9]{2}"
'Day LetterMonth Year(4 digits)
arrPattern(10) = "(0?[1-9]|[12][0-9]|3[01])[\-\.\/\s]((?:Jan(?:uary)?|Feb(?:ruary)?|Mar(?:ch)?|Apr(?:il)?|May|Jun(?:e)?|Jul(?:y)?|Aug(?:ust)?|Sep(?:tember)?|Sept|Oct(?:ober)?|Nov(?:ember)?|Dec(?:ember)?))[- /.]\s?(19|20)[0-9]{2}"
'Year(4 digits) LetterMonth Day
arrPattern(11) = "(19|20)[0-9]{2}[- /.]\s?((?:Jan(?:uary)?|Feb(?:ruary)?|Mar(?:ch)?|Apr(?:il)?|May|Jun(?:e)?|Jul(?:y)?|Aug(?:ust)?|Sep(?:tember)?|Sept|Oct(?:ober)?|Nov(?:ember)?|Dec(?:ember)?))[\-\.\/\s](0?[1-9]|[12][0-9]|3[01])"
'Year(4 digits) Day LetterMonth
arrPattern(12) = "(19|20)[0-9]{2}[- /.]\s?(0?[1-9]|[12][0-9]|3[01])[\-\.\/\s]((?:Jan(?:uary)?|Feb(?:ruary)?|Mar(?:ch)?|Apr(?:il)?|May|Jun(?:e)?|Jul(?:y)?|Aug(?:ust)?|Sep(?:tember)?|Sept|Oct(?:ober)?|Nov(?:ember)?|Dec(?:ember)?))"
'LetterMonth Day Year(2 digits)
arrPattern(13) = "((?:Jan(?:uary)?|Feb(?:ruary)?|Mar(?:ch)?|Apr(?:il)?|May|Jun(?:e)?|Jul(?:y)?|Aug(?:ust)?|Sep(?:tember)?|Sept|Oct(?:ober)?|Nov(?:ember)?|Dec(?:ember)?))[\-\.\/\s](0?[1-9]|[12][0-9]|3[01])[- /.,]\s?[0-9]{2}"
'Day LetterMonth Year(2 digits)
arrPattern(14) = "(0?[1-9]|[12][0-9]|3[01])[\-\.\/\s]((?:Jan(?:uary)?|Feb(?:ruary)?|Mar(?:ch)?|Apr(?:il)?|May|Jun(?:e)?|Jul(?:y)?|Aug(?:ust)?|Sep(?:tember)?|Sept|Oct(?:ober)?|Nov(?:ember)?|Dec(?:ember)?))[- /.]\s?[0-9]{2}"
'Year(2 digits) LetterMonth Day
arrPattern(15) = "[0-9]{2}[- /.]\s?((?:Jan(?:uary)?|Feb(?:ruary)?|Mar(?:ch)?|Apr(?:il)?|May|Jun(?:e)?|Jul(?:y)?|Aug(?:ust)?|Sep(?:tember)?|Sept|Oct(?:ober)?|Nov(?:ember)?|Dec(?:ember)?))[\-\.\/\s](0?[1-9]|[12][0-9]|3[01])"
'Year(2 digits) Day LetterMonth
arrPattern(16) = "[0-9]{2}[- /.]\s?(0?[1-9]|[12][0-9]|3[01])[\-\.\/\s]((?:Jan(?:uary)?|Feb(?:ruary)?|Mar(?:ch)?|Apr(?:il)?|May|Jun(?:e)?|Jul(?:y)?|Aug(?:ust)?|Sep(?:tember)?|Sept|Oct(?:ober)?|Nov(?:ember)?|Dec(?:ember)?))"
'LetterMonth Year(4 digits)
arrPattern(17) = "((?:Jan(?:uary)?|Feb(?:ruary)?|Mar(?:ch)?|Apr(?:il)?|May|Jun(?:e)?|Jul(?:y)?|Aug(?:ust)?|Sep(?:tember)?|Sept|Oct(?:ober)?|Nov(?:ember)?|Dec(?:ember)?))[\-\.\/\s]\s?(19|20)[0-9]{2}"
'Year(4 digits) LetterMonth
arrPattern(18) = "(19|20)[0-9]{2}[\-\.\/\s]\s?((?:Jan(?:uary)?|Feb(?:ruary)?|Mar(?:ch)?|Apr(?:il)?|May|Jun(?:e)?|Jul(?:y)?|Aug(?:ust)?|Sep(?:tember)?|Sept|Oct(?:ober)?|Nov(?:ember)?|Dec(?:ember)?))"
'LetterMonth Day
arrPattern(19) = "((?:Jan(?:uary)?|Feb(?:ruary)?|Mar(?:ch)?|Apr(?:il)?|May|Jun(?:e)?|Jul(?:y)?|Aug(?:ust)?|Sep(?:tember)?|Sept|Oct(?:ober)?|Nov(?:ember)?|Dec(?:ember)?))[\-\.\/\s]\s?(0?[1-9]|[12][0-9]|3[01])\b"
'Day LetterMonth
arrPattern(20) = "(0?[1-9]|[12][0-9]|3[01])[\-\.\/\s]((?:Jan(?:uary)?|Feb(?:ruary)?|Mar(?:ch)?|Apr(?:il)?|May|Jun(?:e)?|Jul(?:y)?|Aug(?:ust)?|Sep(?:tember)?|Sept|Oct(?:ober)?|Nov(?:ember)?|Dec(?:ember)?))"
'LetterMonth
arrPattern(21) = "((?:Jan(?:uary)?|Feb(?:ruary)?|Mar(?:ch)?|Apr(?:il)?|May|Jun(?:e)?|Jul(?:y)?|Aug(?:ust)?|Sep(?:tember)?|Sept|Oct(?:ober)?|Nov(?:ember)?|Dec(?:ember)?))"
Set oRE = CreateObject("VBScript.RegExp")
Set collDates = New Collection
With oRE
.Global = True
.IgnoreCase = True
On Error Resume Next
For i = 1 To 25
.Pattern = arrPattern(i)
Set oMatches = .Execute(strText)
For Each oMatch In oMatches
collDates.Add oMatch.Value, oMatch.Value
Next
Next
End With
'remove dates not in original text
For i = collDates.Count To 1 Step -1
With oRE
.Pattern = "(^|[ \x0A\,\.])" & collDates(i) & "($|[ \x0A\,\.])"
Set oMatches = .Execute(strText)
If oMatches.Count = 0 Then _
collDates.Remove collDates(i)
End With
Next
For i = 1 To collDates.Count
strDates = strDates & collDates(i) & "|"
Next
Set collDates = Nothing
Set oMatches = Nothing
Set oRE = Nothing
If strDates <> "" Then strDates = Left(strDates, Len(strDates) - 1)
If Left(strDates, 1) = "|" Then
DatesInText = Replace(strDates, "|", "", 1, 1)
Else
DatesInText = strDates
End If
End Function
Not sure where slowness came into it as that, so far has not been an issue. I was mainly curious if the code could be modified for a particular set of patterns without commenting out various lines for cases where I wanted to narrow down what is returned and thus in those instances, if the pattern failed, then we wouldn't capture that value. Now that I think about it, if I could figure out how to include the pattern number in the output, then I can see which one(s) succeeded and make a decision that way.I would run the function against a file of the normal size. If you don't find it to be slow
arrPattern(8) = "(19|20)[0-9]{2}(0[1-9]|1[012])(0[1-9]|[12][0-9]|3[01]){1,2}" 'yyyymmdd must be 4 digit yr 2 digit mo 2 digit dt and no sep char
Hi Mark,
That is already there under Pattern 8 and we do use that format for incorporation into some of our file names.
Code:arrPattern(8) = "(19|20)[0-9]{2}(0[1-9]|1[012])(0[1-9]|[12][0-9]|3[01]){1,2}" 'yyyymmdd must be 4 digit yr 2 digit mo 2 digit dt and no sep char
you could declare a public enum e.g.Or am I better off using my existing method where I have a single purpose function that I feed it the text and the pattern?
Public Enum DateFormats
shortdate = 1
...
...
MonthYear = 17
etc
End Enum