Solved

Finding if date exists within a random string

Posted on 2016-08-13
17
23 Views
Last Modified: 2016-10-10
Has anyone written MS Access VBA code in the past which tests a variable/random piece of string to check if it contains a date within it?

For example: "123abc 1-11-2015 aaa"
                        "xxxxxxxxxxxxxxx1-6-2016"
                        "12-Dec-2015 aaaaabbbbbbcc"
0
Comment
Question by:Dylan_E
  • 6
  • 5
  • 2
  • +2
17 Comments
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
If the month is always a three character abbreviation, search for that and when you find it, look left three and right 5.  Use IsDate() to determine if the whole string is a date.
0
 
LVL 49

Expert Comment

by:Gustav Brock
Comment Utility
You can use a function like this:
Public Function ContainsDate(ByVal Text As String) As Boolean

    Dim Parts   As Variant
    Dim Item    As Integer
    Dim Found   As Boolean
    
    Parts = Split(Text & " ", " ")
    For Item = LBound(Parts) To UBound(Parts)
        If IsDate(Parts(Item)) Then
            Found = True
            Exit For
        End If
    Next
    
    ContainsDate = Found
    
End Function

Open in new window

Then, to also catch the mid example, run this:

    FoundDate = ContainsDate(Replace(YourString, "x", " "))

/gustav
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
there are an awful lot of combinations of what constitutes a data:

1/1/16
1/1/2016
1/01/16
1/01/2016
01/1/16
01/01/16
01/1/2016
01/01/2016

is just the tip of the iceberg.  Although I assumed month, day, then year, you could also do this as day, month, year, or some other combination.  Then you could also throw in the combinations using the three letter month abbreviation or even the full month name, so you are probably talking about over a hundred combinations.

Gustav's function above would probably catch quite a few of those, although it would exclude any where the month name or abbreviation was used and separated from the day or year by a space.  Furthermore, almost any number will pass the IsDate( ) check so you have to be concerned with that as well.

Sorry I cannot provide more help.

Dale
0
 
LVL 30

Expert Comment

by:hnasr
Comment Utility
From the data presented as an example, you need to check for the existence "-" in the string, more than once.
Post few records and the expected output. Is the issue like this?

"123abc 1-11-2015 aaa"                      1-11-2015       one digit day.
"xxxxxxxxxxxxxxx1-6-2016"               1-6-2016         one digit, day and month, and no space before day.
"12-Dec-2015 aaaaabbbbbbcc"         12-Dec-2015   month short name.

Is this a data preparation, entry,  problem?
0
 

Author Comment

by:Dylan_E
Comment Utility
hnasr you are on the right track to understanding my question based on my examples. Looking for two "-" characters will be a good starting point.

The nature of this problem is that I am receiving Excel files from clients via email. Some of them modify the name of the file to include the date and some don't.

My program needs to check if there is a date in the filename AND then compare it to the date within the file. Date in the file and date in the name have to be the same, otherwise it goes into an error log file.

Filenames can only have "-" for the date because "/" is not allowed in window's file names.
0
 

Author Comment

by:Dylan_E
Comment Utility
and...there can be many variations, since there is no way of controlling what clients type in.
0
 
LVL 30

Expert Comment

by:hnasr
Comment Utility
since there is no way of controlling what clients type in
1st. At least agreed format should be implemented between you and clients.
2nd. We have to agree on a way to help you in the issue.

I didn't understand the part:
My program needs to check if there is a date in the filename AND then compare it to the date within the file. Date in the file and date in the name have to be the same, otherwise it goes into an error log file.
Try to explain with sample data.
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 30

Expert Comment

by:hnasr
Comment Utility
One way, that I am aware of, is to have the image in the Form header, and highlight the records with the same birth day.
0
 

Author Comment

by:Dylan_E
Comment Utility
1st - there will be no agreed format with the clients, because i know it is possible to write code which will pick out the date from a random string. I was hoping somebody already did it in the big wide inter-web and would be kind enough to share it, so i don't have to re-invent the wheel myself.

2nd - I will try to clarify my instruction

Here is the clarification for the paragraph you didn't understand -

I am sending a survey Excel file to client's, which they are completing daily. For example:

"Survey 1 (ID:123).xlsx"

They keep this file on their computer and each day they open it, change the date header inside the file and send it back.

Sometimes they also change the filename eg. "Survey 1 15-Aug-2016 (ID:123).xlsx"

Then they reuse the file the next day, but they change the date inside the file but not outside the file. So what we have now is 16-Aug-2016 on the inside of the file, but 15-Aug-2016 in the filename.

I would like to be able to pick this up and say..."Hang on...the date on the inside of the file is different to the outside of the file."

I hope this helps. If you have any more questions, please let me know. Thanks.
0
 
LVL 30

Expert Comment

by:hnasr
Comment Utility
My last comment was meant for another thread. Please Ignore.
0
 
LVL 30

Expert Comment

by:hnasr
Comment Utility
1st - there will be no agreed format with the clients, because i know it is possible to write code which will pick out the date from a random string.
I am sending a survey Excel file to client's, which they are completing daily.

Sorry, I can't help for a one time job. This job can be controlled by you since you send the original file.
You may allow filling necessary cells for required info. Verification may be done in the Excel document itself.
0
 

Accepted Solution

by:
Dylan_E earned 0 total points
Comment Utility
Ok, here it is. I wrote it myself.

This code picks up any date format within any random string.

Please feel free to review and provide your comments.

p.s. Feel free to use if you ever come across this problem in the future.

Thank you for taking the time to comment on my post.
Date-In-Name.accdb
0
 
LVL 49

Expert Comment

by:Gustav Brock
Comment Utility
It works for the examples you provided, which we must trust are representative.

However, when I put a date stamp in a filename, I use 20160815 as there is no reason to type more than needed as these stamps typically are for sorting only. This format, your function doesn't catch but, of course, if you don't receive such stamps that will not create an issue.

/gustav
0
 

Author Comment

by:Dylan_E
Comment Utility
Gustav,

In that situation, we could use the code i provided as a base, but then create the additional checks, which will pick up any set of numbers, which contain a certain number of digits and contain 2016 or 2017 etc.

Good point you make, but for my purposes, the users mostly use standard date formats as I described.

Thanks again for taking the time to have a look.
0
 

Author Comment

by:Dylan_E
Comment Utility
Please undo the selected answer. I was meant to click on the one containing the attachment. Thanks.
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now