Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Finding if date exists within a random string

Posted on 2016-08-13
17
26 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 36

Expert Comment

by:PatHartman
ID: 41754844
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
ID: 41754851
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)
ID: 41754904
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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 30

Expert Comment

by:hnasr
ID: 41755483
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
ID: 41755779
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
ID: 41755781
and...there can be many variations, since there is no way of controlling what clients type in.
0
 
LVL 30

Expert Comment

by:hnasr
ID: 41755788
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
 
LVL 30

Expert Comment

by:hnasr
ID: 41755803
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
ID: 41755807
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
ID: 41755815
My last comment was meant for another thread. Please Ignore.
0
 
LVL 30

Expert Comment

by:hnasr
ID: 41755821
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
ID: 41755934
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
ID: 41755951
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
ID: 41755970
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
ID: 41755977
Please undo the selected answer. I was meant to click on the one containing the attachment. Thanks.
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

837 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