Checking for date (1 Viewer)

Kundan

Registered User
Joined
Mar 23, 2019
Messages
78
How to check whether a Text field contents contain a date?
 

arnelgp

error reading drive A:
Joined
May 7, 2009
Messages
8,629
use IsDate() function:
Code:
If IsDate([yourTextField])
    'valid date date
Else
    'not valid date
End If
 

Kundan

Registered User
Joined
Mar 23, 2019
Messages
78
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.
 

Gasman

Enthusiastic Amateur
Joined
Sep 21, 2011
Messages
5,016
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.
You would need to find the space before the date as I expect that text is going to change.?
What happens is someone does not leave a space in between?

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.?

If you are sure there will always be a space, use InstrRev() to locate the position of the space " ", use Mid() function to get the date portion and then the IsDate() function to check that.

EG
Code:
str1="Feast on might be on 08-08-19"
? IsDate(Mid(str1,InstrRev(str1," ")+1))
True
HTH
 
Last edited:

arnelgp

error reading drive A:
Joined
May 7, 2009
Messages
8,629
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
 

NauticalGent

Executive Troll
Joined
Apr 27, 2015
Messages
2,419
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?
Kundan,

If you take nothing else from the advice given, Gasman's advice about splitting this info into two separate fields is the one you should heed the most.

It will fix your problem and allow you to do more with the dates later as you will surely need to do so.
 

Pat Hartman

Super Moderator
Staff member
Joined
Feb 19, 2002
Messages
28,534
Why do people mush data like this? Do you think you have to pay to add a new field to a table? Do you think it is somehow more efficient to mush data that should be in several fields into one? No, you don't but you sure do have to pay to fix the problem once you've made this mistake.

Please, Please, Please, don't mush attributes. Every attribute belongs in a separate field. If you want to see two fields together on reports, concatenate them. It is simple to string them all together for reporting purposed but it is never simple to separate them when you need to work with the individual attributes.
 

NauticalGent

Executive Troll
Joined
Apr 27, 2015
Messages
2,419
I propose that we officially call this bad design habit "Attribute Mushing". Simple, concise and self explanatory.
 

Kundan

Registered User
Joined
Mar 23, 2019
Messages
78
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
Thanks a lot! GOD BLESS YOU!!!!!!!!!
 

Kundan

Registered User
Joined
Mar 23, 2019
Messages
78
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.
 

NauticalGent

Executive Troll
Joined
Apr 27, 2015
Messages
2,419
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.
As my father was fond of telling me: "You can TAKE time to do it right, or you can MAKE time to do it over."
 

Pat Hartman

Super Moderator
Staff member
Joined
Feb 19, 2002
Messages
28,534
I'm with Gent. Every single time I have taken a design shortcut or failed to fix one made by someone else, I regretted it later.

arne wrote the code for you. Fix it once and be done with the problem.
 

sxschech

Registered User
Joined
Mar 2, 2010
Messages
636
Saw this post and looks like it may help cover lots of date variations under "one roof" as my current set up is using one or more regex functions where I pass the string and pattern and continue to add individual patterns when discovered. Before I get an "Attribute Mush" scold, would like to mention that I am using something like this in order to extract dates from text in email subject/body and/or word docs that are then reformatted to be used in standardizing a file name and/or a specific text item in a word doc that will be converted to pdf and not stored in an access table/field. I made a few modifications to handle a couple of conditions that weren't in the original code.

  • Reduced number of arrPatterns by allowing leading zeroes to be optional
    08/01/2019 can be handled in the same pattern as 8/1/2019
  • Allow month to be fully spelled out 15-Aug-2019 can be handled in the same pattern as 15-August-2019

  • Changed delimiter from comma (,) to pipe (|) to allow for comma in date
  • Added patterns for Month and Year OR Year and Month (No Day)
    Month and Day OR Day and Month (No Year)
    Month only

    Why? Because some files are to be Named with month and year so user will provide text like "System Report for August" which we then will convert to the proper naming convention based on month only.

I'm hoping that I have the patterns in a logical order so in general, when it returns the text, if there are multiple items, I can rely on the split function (0) to provide the pattern.
Code:
? datesintext("This is the report from 15 August 2019 for review")
15 August 2019|August 2019|15 August|August
An enhancement question, if I may. If I know up front that I want to only evaluate a particular pattern or patterns, how can I pass that information to the function? Let's say, using above example, I know that the pattern I want returned is August 2019, as found from arrPattern(17). Assuming this can be done Could it be like:
Code:
datesintext("This is the report from 15 August 2019 for review",17)
or alternatively and easier to remember something like:
Code:
datesintext("This is the report from 15 August 2019 for review","MonthYear")
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?

Modified code for extracting the date from text string:
Code:
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
 

Pat Hartman

Super Moderator
Staff member
Joined
Feb 19, 2002
Messages
28,534
I would run the function against a file of the normal size. If you don't find it to be slow, just leave all the patterns in the code. If it is too slow, comment out the patterns you are sure won't exist. I would just add 20 quotes in front of them so they shift far enough to the right to not disturb the flow of the code.

When you get data from sources over which you have no control, you have to deal with a lot of crap. Having to parse strings this way is downright dangerous. You never know when they'll slip in some sneaky variation and unless some user complains, you'll never know.

So, best practice is to never do this to yourself or to anyone else and fix it permanently when you import it so it doesn't stay mushed in your tables.
 

sxschech

Registered User
Joined
Mar 2, 2010
Messages
636
My current plan is to run the code as is where appropriate.
I would run the function against a file of the normal size. If you don't find it to be slow
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.

Using this code is about seeing and learning the capabilities of what can or can't be extracted and manipulated via code rather than manually editing, reformatting or copy pasting text. I still review and can further edit the result before clicking ok, so it is not blindly getting edited. Still don't fully understand as much as I'd like about regex, but am trying to apply and figure it out to see if I can make it work and it has been interesting to see how much can be done that previously I was doing by hand.
 

Mark_

Longboard on the internet
Joined
Sep 12, 2017
Messages
2,112
One pattern that may need to be added, depending on who deals with governments, is

YYYYMMDD

Seeing 20190816 can really mess with you if you don't know its supposed to be a date, but is also really handy for naming files to keep them in date order.
 

sxschech

Registered User
Joined
Mar 2, 2010
Messages
636
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
 

NauticalGent

Executive Troll
Joined
Apr 27, 2015
Messages
2,419
@ArnelGP,

This is probably the best use of Regular Expressions I have seen to date. I appreciate you sharing and I am adding this one to the library.

Doubt if I will ever need this as it is written but it will serve as tutorial should I need to do something similar.

Thanks again!
 

Mark_

Longboard on the internet
Joined
Sep 12, 2017
Messages
2,112
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
Missed this... Then again Arnel does have a fantastic piece of code that covers A LOT..
 

CJ_London

Super Moderator
Staff member
Joined
Feb 19, 2013
Messages
11,574
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?
you could declare a public enum e.g.

Code:
Public Enum DateFormats
  shortdate = 1 
   ...
   ...
  MonthYear = 17
  etc
End Enum
then make the parameter optional - you then have the choice- leave it blank to run through all, or enter MonthYear or 17 as you wish
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom