Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 08-07-2019, 09:12 PM   #1
Kundan
Newly Registered User
 
Join Date: Mar 2019
Posts: 22
Thanks: 9
Thanked 0 Times in 0 Posts
Kundan is on a distinguished road
Checking for date

How to check whether a Text field contents contain a date?

Kundan is offline   Reply With Quote
Old 08-07-2019, 09:19 PM   #2
arnelgp
Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 7,992
Thanks: 64
Thanked 2,545 Times in 2,444 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Checking for date

use IsDate() function:
Code:
If IsDate([yourTextField])
    'valid date date
Else
    'not valid date
End If
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 08-08-2019, 12:31 AM   #3
Kundan
Newly Registered User
 
Join Date: Mar 2019
Posts: 22
Thanks: 9
Thanked 0 Times in 0 Posts
Kundan is on a distinguished road
Re: Checking for date

Quote:
Originally Posted by arnelgp View Post
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.

Kundan is offline   Reply With Quote
Old 08-08-2019, 01:05 AM   #4
Gasman
Enthusiastic Amateur
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 3,978
Thanks: 417
Thanked 714 Times in 693 Posts
Gasman has a spectacular aura about Gasman has a spectacular aura about Gasman has a spectacular aura about
Re: Checking for date

Quote:
Originally Posted by Kundan View Post
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
__________________
Access novice. Sometimes trying to give something back.
Access 2007

Please, please use code tag # when posting code snippets

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by Gasman; 08-08-2019 at 01:22 AM.
Gasman is offline   Reply With Quote
Old 08-08-2019, 01:39 AM   #5
arnelgp
Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 7,992
Thanks: 64
Thanked 2,545 Times in 2,444 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Checking for date

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
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
The Following 3 Users Say Thank You to arnelgp For This Useful Post:
Kundan (08-08-2019), NauticalGent (08-16-2019), sxschech (08-15-2019)
Old 08-08-2019, 02:21 AM   #6
NauticalGent
Pristine Curmudgeon
Gold Supporter
 
NauticalGent's Avatar
 
Join Date: Apr 2015
Location: Naples, Italy (EXPAT from Virginia)
Posts: 1,916
Thanks: 420
Thanked 277 Times in 243 Posts
NauticalGent has a spectacular aura about NauticalGent has a spectacular aura about
Re: Checking for date

Quote:
Originally Posted by Gasman View Post
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.
__________________
- Ciao, John
NauticalGent is offline   Reply With Quote
Old 08-08-2019, 08:27 PM   #7
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 27,967
Thanks: 13
Thanked 1,535 Times in 1,461 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: Checking for date

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.

__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 08-08-2019, 09:25 PM   #8
NauticalGent
Pristine Curmudgeon
Gold Supporter
 
NauticalGent's Avatar
 
Join Date: Apr 2015
Location: Naples, Italy (EXPAT from Virginia)
Posts: 1,916
Thanks: 420
Thanked 277 Times in 243 Posts
NauticalGent has a spectacular aura about NauticalGent has a spectacular aura about
Re: Checking for date

I propose that we officially call this bad design habit "Attribute Mushing". Simple, concise and self explanatory.
__________________
- Ciao, John
NauticalGent is offline   Reply With Quote
The Following User Says Thank You to NauticalGent For This Useful Post:
Kundan (08-08-2019)
Old 08-08-2019, 11:28 PM   #9
Kundan
Newly Registered User
 
Join Date: Mar 2019
Posts: 22
Thanks: 9
Thanked 0 Times in 0 Posts
Kundan is on a distinguished road
Re: Checking for date

Quote:
Originally Posted by arnelgp View Post
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 is offline   Reply With Quote
Old 08-08-2019, 11:31 PM   #10
Kundan
Newly Registered User
 
Join Date: Mar 2019
Posts: 22
Thanks: 9
Thanked 0 Times in 0 Posts
Kundan is on a distinguished road
Re: Checking for date

Quote:
Originally Posted by NauticalGent View Post
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.
Kundan is offline   Reply With Quote
Old 08-09-2019, 12:07 AM   #11
NauticalGent
Pristine Curmudgeon
Gold Supporter
 
NauticalGent's Avatar
 
Join Date: Apr 2015
Location: Naples, Italy (EXPAT from Virginia)
Posts: 1,916
Thanks: 420
Thanked 277 Times in 243 Posts
NauticalGent has a spectacular aura about NauticalGent has a spectacular aura about
Re: Checking for date

Quote:
Originally Posted by Kundan View Post
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."
__________________
- Ciao, John
NauticalGent is offline   Reply With Quote
Old 08-09-2019, 07:31 AM   #12
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 27,967
Thanks: 13
Thanked 1,535 Times in 1,461 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: Checking for date

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.
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 08-15-2019, 11:41 AM   #13
sxschech
Newly Registered User
 
Join Date: Mar 2010
Posts: 593
Thanks: 23
Thanked 110 Times in 99 Posts
sxschech is on a distinguished road
Re: Checking for date

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
sxschech is offline   Reply With Quote
Old 08-15-2019, 06:06 PM   #14
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 27,967
Thanks: 13
Thanked 1,535 Times in 1,461 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: Checking for date

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.
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 08-16-2019, 10:29 AM   #15
sxschech
Newly Registered User
 
Join Date: Mar 2010
Posts: 593
Thanks: 23
Thanked 110 Times in 99 Posts
sxschech is on a distinguished road
Re: Checking for date

My current plan is to run the code as is where appropriate.
Quote:
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.

sxschech is offline   Reply With Quote
Reply

Tags
checking for date

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Checking date against a text box oMADMANo Visual Basic 0 01-25-2012 02:48 AM
Checking staff against booking date? bazzanoid Forms 9 10-22-2008 11:40 PM
Checking for date Tony1258 Modules & VBA 2 08-21-2006 07:51 AM
[SOLVED] Checking date availability rsmayze001 General 1 01-05-2006 12:38 PM
[SOLVED] Checking a previous date against present date Glyn Queries 2 05-04-2005 05:04 AM




All times are GMT -8. The time now is 07:17 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World