Complex - Extract String and Convert to Date

[jrose]

I am by no means an Access or VBA expert; but this is, in my opinion, a very advanced way of programming that will require complex coding if it is even possible--and I need all the help I can get. Below I have provided much detail in a way that, I hope, makes sense to anyone reading it.

We've recently run into a problem with the part of our company database that locates a variety of documents and stores a list of the file names and file locations in a temporary table. This data is compiled from multiple file directories throughout our network based on string criteria and provides the user with an easy to view list formatted using Access 2010. All documents are filed and named specifically and consistently using a different Access database. The file location strings that we use are set, accurate, and should not need modifying. The file name strings, however, are where we've run into a problem. The file names are formatted in this way: "ClientName_yyyy_mm_dd_Document_Name" or "ClientName_yyyy_Document_Name". The problem we have lies within the date (notice that files can be named with the full date or just the year). Our users have requested the ability to edit the document names that are stored/displayed based on a specific date range. Of course, we can use a string filter if we wanted to list documents from one specific date, but they would like a range of dates. In order to do this, I believe, we would have to find a way to search for a specific type of string (based on possible characters used and the format it would be in) in each of the file names, convert this string into a date format that Access recognizes, then perform a mathematical date function to determine if that date falls within the specified range. I'm quite certain that if we are able to extract the appropriate string section then converting that string into a usable date shouldn't be too difficult (we could possibly use the DateValue function or something similar); and if that is achieved then determining whether the date is within range should simply require a precisely calculated If statement. The difficult part, in my opinion, will be the initial event of extracting the string section needed to make all this work. Since the actual string date value will vary with each file name, it is obvious that we cannot search for anything specific. Somehow we will need to create, possibly, a function/module that will recognize when any combination of specified characters (in this case, numbers 0-9) are used in one of two formats ("_####_##_##_" or "_####_"). It will also probably be necessary to include a way to ensure that if more than one of these string sections occur in a file name (which it will because some documents do have two dates), we can then specify which one will be chosen (I am not sure at this time if the first or second date would be preferred by users). Another thing to account for is the fact that the full-date string format contains the same type of string section that would be found when searching for just the year ("_####_"). Therefore, it will be necessary to check for the full-date format first and if that does not exist then check for just the year. If accomplished we will simply then have to tell Access that this string "_####_##_##_" equals this date "_yyyy_mm_dd_" which can hopefully then be recognized, for example, in the short date format (mm/dd/yyyy) which we can then compare to other date variables and perform an action based on that outcome.

Again, I hope that I have explained what we are trying to accomplish in a way that makes sense. If needed, I will provide further information on whatever I am able as it is requested. Unfortunately, I cannot supply any examples of our current code, but I will do my best to explain what it is our existing code does and how if asked of me. I do not expect for someone to successfully compose all of the code necessary to make this possible. I will simply accept and appreciate any and all help given to me--from only being able to inform me of whether or not this feat is even possible, to pointing me in the direction of making it possible, or possibly to providing me with workable code examples of your own (no matter how big or small). Many thanks in advance.

[Bulzie]

Are they only wanting to search by Year as some of the file names only have year? Would ClientName ever have underscore in it or the first underscore will always proceed the Year? You could count the underscores I guess to see if it is only year or full date.

[Bulzie]

Something like this would parse out the full date or just the year I believe.

vYear = Mid([FileName], (InStr([FileName], "_")) + 1, 4)

'Check to see if has full date, if so calculate Month and Day
IF Mid([FileName], (InStr([FileName], "_")) + 8, 1) = "_" Then

vMonth = Mid([FileName], (InStr([FileName], "_")) + 6, 2)
vDay = Mid([FileName], (InStr([FileName], "_")) + 9, 2)

End IF

[jrose]

Yes, the user is given the option of choosing a full date or just a year to be included in the file name. Searching "#_#" would not work because there is a client identification number that precedes the year. For example: ClientName1234_yyyy_mm_dd_Document_Name. Sorry I did not mention that before. Also, some documents will have a second date and second identification number tacked onto the end after the document name (I know this greatly complicates things). The first underscore will almost always be the one right before the year. Some clients have a "Jr" or "Sr" in their name which will be accompanied with an underscore (ClientName_Jr1234). I do not like that it was done that way, but it can't be changed now.

[Bulzie]

Review my previous post with the parsing. You could check the value after the first "_" to see if numeric or not to decide if it starts the year or not. So the first date is the only one you need to parse and search on, not the other dates that could be in the name?

Can you give actual examples of the different types of filenames.

[jrose]

Thank you for your suggestion. I can see how something like that could work, but only for the majority of clients whose name does not contain a Jr or Sr. Also, if there is no month or day in the file name then how will the code know if it needs to fill those variables? Without a way to differentiate between letters and numbers wouldn't it recognize whatever characters are in that specified location regardless of whether or not they are actually part of a date? I feel like an If statement or something to that effect would be needed before running the code for the month and day. Otherwise we may end up trying to use letter combinations in a date format. Again, thank you for your response and any insight you are able to give is appreciated.

[jrose]

Okay, a numeric check would do the trick. The first date should be the one they want to use. That's what I would put my money on at least. I would have to verify that with our end users. The first date, I believe, is the date which a document is originally created, the second date is when the document is finalized and saved. Here are a few sample file names that could occur.

RoseJ1234_2016_Progress_Report
RoseJ1234_2016_08_19_Progress_Report
RoseJ_Jr1234_2016_Progress_Report
RoseJ_Jr1234_2016_08_19_Progress_Report
RoseJ1234_2016_08_10_Progress_Report_2016_08_19_12 34_5678
RoseJ_Jr1234_2016_08_10_Progress_Report_2016_08_19 _1234_5678

[Bulzie]

You can use IsNumeric(x) to check to see if it a character is a number or letter. So the idea is:
1. Find the first "_", check to see if the next character is a letter or number.
2. If letter (Jr, Sr), then look for the next "_", once found check again to see if next char is a number. This should start your year(next 4 characters).
3. Check the 6th character after that to see if it is numeric, if it is hat starts the Month, if it is not then this FileName only has a year. If you do have a Month then you know you have a Day so can find that as well.

[Bulzie]

Does it always have the actual words "Progress_Report" after the Date?

[orange]

Here is a sample function and test routine that may be helpful.
I used your original post to create test data. I see you have modified the layout since.

Here is the function:

'---------------------------------------------------------------------------------------
' Procedure : ValidateVBa
' Author    : mellon
' Date      : 19-Aug-2016
' Purpose   : Demo routine to identify whether or not one of 2 patterns is present.
' Patterns are:
'a)YYYY_MM_DD_
'where YYYY is 1... or 2... (... 3 digits) and
' MM is [0-1][0-9]   01 --12
' DD is [0-3][0-9]   01 --31
' _ is underscore char within file name
'
'b)YYYY_ where YYYY is as above.
'
'There is not necessarily an underscore before the YYYY, so not tested
'---------------------------------------------------------------------------------------
'
Function ValidateVBa(S As String) As String
10       On Error GoTo ValidateVBa_Error

20        If Len(S) & "" = 0 Then Exit Function
30        If S Like "*[1-2][0-9][0-9][0-9]_[0-1][0-9]_[0-3][0-9]*" Then
40            Debug.Print "--------S has a date YYYY_MM_DD_  " & S
50        ElseIf S Like "*[1-2][0-9][0-9][0-9]_*" Then
60            Debug.Print "-+-+-+-S has a date YYYY Only " & S
70        Else
80            Debug.Print "========not a match " & S
90        End If

100      On Error GoTo 0
110      Exit Function

ValidateVBa_Error:

120       MsgBox "Error " & err.number & " in line " & Erl & " (" & err.Description & ") in procedure ValidateVBa of Module AWF_Related"
End Function

Here is the test routine:

Sub testffinder()
Dim filen(7) As String, i As Integer
filen(0) = "GeorgeBrown_2010_03_21_InvoiceRevision.doc"
filen(1) = "TomSmith_1988_June_9_Report.rpt"
filen(2) = "SamuelsenInc_2006_09_17_DispatchNotes.txt"
filen(3) = "JorgensenLtd_2006_08_11_DispatchNotes.txt"
filen(4) = "ThamuelsenInc_2006_DispatchNotes.txt"
filen(5) = "SamsClub_2006_09_DispatchNotes.txt"
filen(6) = "BobEvans_46_RevisionNotes.txt"
filen(7) = "ARedHerring_Notes.txt"

For i = 0 To 7
Debug.Print i & " " & ValidateVBa(filen(i))
Next i
End Sub

And here is the test output:

--------S has a date YYYY_MM_DD_ GeorgeBrown_2010_03_21_InvoiceRevision.doc
0
-+-+-+-S has a date YYYY Only TomSmith_1988_June_9_Report.rpt
1
--------S has a date YYYY_MM_DD_ SamuelsenInc_2006_09_17_DispatchNotes.txt
2
--------S has a date YYYY_MM_DD_ JorgensenLtd_2006_08_11_DispatchNotes.txt
3
-+-+-+-S has a date YYYY Only ThamuelsenInc_2006_DispatchNotes.txt
4
-+-+-+-S has a date YYYY Only SamsClub_2006_09_DispatchNotes.txt
5
========not a match BobEvans_46_RevisionNotes.txt
6
========not a match ARedHerring_Notes.txt
7


Good luck with the project

[jrose]

Thank you very much for your help, that seems like an excellent place to start. And no, there are many different types of documents with varying amounts of different words; I can't recall any that would include numbers if you're curious about that.

[jrose]

Thank you, orange. Yes, sorry I was not more specific about the document name layout in my original post. What you have provided is helpful nonetheless.