Find latest date imbedded in filename within specific folder (1 Viewer)

NewbieX

New member
Local time
Today, 03:39
Joined
Apr 11, 2016
Messages
7
I have a backup directory with the the date in the filenames to mark when a database was backup-ed. I want to search the directory and return the filename with the latest backup date. (Not last modified date)

Format looks like this (always)

FileName_mmddyyyy.accdb

Thus in C:\BackupDbDir might have the following Accounts database files

Accounts_02022014.accdb
Accounts_02032014.accdb
Accounts_02022015.accdb

I want to return the variable = Accounts_02022015.accdb

I figured out how to loop through directory and extract date information but am stuck from there. I do not know how to turn these into dates and compare.

Ideas?

Code:
Dim f, tmp

f = Dir("C:\BackupDbDir\Accounts_*.accdb")
Do While Len(f) > 0
    tmp = Mid(f, 6, 8)
    Debug.Print tmp
    f = Dir()
Loop
 

MarkK

bit cruncher
Local time
Today, 03:39
Joined
Mar 17, 2004
Messages
8,178
You could do something like . . .
Code:
Private Sub Test91476983471()
    Dim f As String
    Dim tmp As String
    Dim dTmp As Date
    Dim dMax As Date

    f = Dir("C:\BackupDbDir\Accounts_*.accdb")
    Do While Len(f) > 0
        tmp = Mid(f, 6, 8)
        dTmp = CDate(Format(tmp, "00/00/0000"))
        If dTmp > dMax Then dMax = dTmp
        f = Dir()
    Loop
    Debug.Print dMax
End Sub
 

NewbieX

New member
Local time
Today, 03:39
Joined
Apr 11, 2016
Messages
7
I get a a Run time Error 13, Type mismatch at this line:

Code:
dTmp = CDate(Format(tmp, "00/00/0000"))
I tried putting "#00/00/0000#" but did not help.
 

MarkK

bit cruncher
Local time
Today, 03:39
Joined
Mar 17, 2004
Messages
8,178
On the first loop?
Possibly the pattern of the date as you've saved it in your filename, and the Windows date format as expected by CDate() are not the same. Let's check if we are getting a valid date first . . .
Code:
Private Sub Test91476983471()
    Dim f As String
    Dim tmp As String
    Dim dTmp As Date
    Dim dMax As Date

    f = Dir("C:\BackupDbDir\Accounts_*.accdb")
    Do While Len(f) > 0
        tmp = Mid(f, 6, 8)
        debug.print tmp, IsDate(Format(tmp, "00/00/0000"))
[COLOR="Green"]'        dTmp = CDate(Format(tmp, "00/00/0000"))
'        If dTmp > dMax Then dMax = dTmp
[/COLOR]        f = Dir()
    Loop
    Debug.Print dMax
End Sub
Are any of the formatted dates valid???
 

NewbieX

New member
Local time
Today, 03:39
Joined
Apr 11, 2016
Messages
7
Results are thus:

02022014 False
02032014 False
02022015 False

On the first loop?
Possibly the pattern of the date as you've saved it in your filename, and the Windows date format as expected by CDate() are not the same. Let's check if we are getting a valid date first . . .
Code:
Private Sub Test91476983471()
    Dim f As String
    Dim tmp As String
    Dim dTmp As Date
    Dim dMax As Date

    f = Dir("C:\BackupDbDir\Accounts_*.accdb")
    Do While Len(f) > 0
        tmp = Mid(f, 6, 8)
        debug.print tmp, IsDate(Format(tmp, "00/00/0000"))
[COLOR=Green]'        dTmp = CDate(Format(tmp, "00/00/0000"))
'        If dTmp > dMax Then dMax = dTmp
[/COLOR]        f = Dir()
    Loop
    Debug.Print dMax
End Sub
Are any of the formatted dates valid???
 

MarkK

bit cruncher
Local time
Today, 03:39
Joined
Mar 17, 2004
Messages
8,178
Replace this . . .
Code:
debug.print tmp, IsDate(Format(tmp, "00/00/0000"))
with this . . .
Code:
debug.print Format(tmp, "00/00/0000"), IsDate(Format(tmp, "00/00/0000"))
and let's see what we get . . .
 

NewbieX

New member
Local time
Today, 03:39
Joined
Apr 11, 2016
Messages
7
02/02/2014 True
02/02/2015 True
02/03/2014 True

Monkeyed with script after last error message and the following seems to work:
Code:
    Do While Len(f) > 0
        tmp = Mid(f, 6, 8)
        'Debug.Print Format(tmp, "00/00/0000"), IsDate(Format(tmp, "00/00/0000"))
        dTmp = (Format(tmp, "00/00/0000"))
        'Debug.Print IsDate(dTmp)
        If dTmp > dMax Then dMax = dTmp
    f = Dir()
    Loop
   ' Debug.Print dMax
Many thanks.

Replace this . . .
Code:
debug.print tmp, IsDate(Format(tmp, "00/00/0000"))
with this . . .
Code:
debug.print Format(tmp, "00/00/0000"), IsDate(Format(tmp, "00/00/0000"))
and let's see what we get . . .
 
Last edited:

MarkK

bit cruncher
Local time
Today, 03:39
Joined
Mar 17, 2004
Messages
8,178
First you reported this
02022014 False
02032014 False
02022015 False
Then you reported this
02/02/2014 True
02/02/2015 True
02/03/2014 True
But we did not change the expression that evaluates the boolean -> IsDate(Format(tmp, "00/00/0000")). Why do we get a different result on something we did not change?
 

NewbieX

New member
Local time
Today, 03:39
Joined
Apr 11, 2016
Messages
7
First you reported this
Then you reported this

But we did not change the expression that evaluates the boolean -> IsDate(Format(tmp, "00/00/0000")). Why do we get a different result on something we did not change?


Only change I see is the removal of "CDate"
 

Users who are viewing this thread

Top Bottom