using DIR to get a max file

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I have files stored as the following fistname_surname dd.mm.yy.tft,

I have a database which sends out emails to relevant people, what I need is
a function which will look through my files and get me the most recent dated
file (not timestamp).

code pointers appreciated
 
Something like the following untested aircode should work:

Function MostRecentFile(FolderName As String) As String

Dim dtmLatest As Date
DIm intFirstSpace As Integer
Dim strFile As String
Dim strFileDate As String
Dim strFolder As String
Dim strLatestFile As String

' Make sure there's a slash at the end
' of the folder name
If Right$(FolderName, 1) <> "\" Then
strFolder = FolderName & "\"
Else
strFolder = FolderName
End If

' Initialize variables
dtmLatest = #12/30/1899#
strLatestFile = vbNullString

strFile = Dir$(strFolder & "*.tft")
Do While Len(strFile) > 0
' Find the space between the name and the date
intFirstSpace = InStr(strFile, " ")
If intFirstSpace > 0 Then
strFileDate = Mid$(strFile, (intFirstSpace + 1), 8)
If CDate(strFileDate) > dtmLatest Then
dtmLatest = CDate(strFileDate)
strLatestFile = strFile
End If
End If
strFile = Dir$()
Loop

MostRecentFile = strLatestFile

End Function


Note that because your dates are in dd.mm.yy format, CDate will only work if
the user's regional settings have the Short Date set to that format (in
Regional Settings). For a more general approach, you might want to create
your own conversion function, such as:

Function Convert_ddmmyy(InputString As String) As Variant

Dim intDay As Integer
Dim intMonth As Integer
Dim intYear As Integer
Dim varReturn As Variant

varReturn = Null

If Len(InputString) = 8 Then
If Mid$(InputString, 3, 1) = "." And _
Mid$(InputString, 6, 1) = "." Then
intDay = CInt(Left$(InputString, 2))
intMonth = CInt(Mid$(InputString, 4, 2))
intYear = CInt(Right$(InputString, 2))
varReturn = DateSerial(intYear, intMonth, intDay)
End If
End If

Convert_ddmmyy = varReturn

End Function
 
Thanks for getting back to me douglas,

I should have mentioned this earlier, the filename which I previously
mentioned as being firstname_surname dd.mm.yy.tft I take the
firstname_surname from the database. ie I have a while loop code which looks
like below

With Test1
Do Until .EOF
If Not IsNull(![EMP_NAME]) And (![STATUS]) = "To Be Sent" And
(![7_DAYS_BEFORE]) = Date And (![MAN_Number]) = 12 Then
Emp = ![EMP_NAME]
NDate = ![7_DAYS_BEFORE] + 7
Manager = (![MANAGER])

strTo = "(e-mail address removed)"
strSubject = "data For: " & Emp & " Due On: " & NDate & " With "
& Manager & " Sent on : " & (![7_DAYS_BEFORE])
strBody = "Data Documents Attached"
FirstFile = "C:\test\Forms\data.zip"
SecondFile = "C:\test\Forms\" & Emp & ".tif"

SendNotesMail strTo, strSubject, strBody, FirstFile, SecondFile,
ThirdFile

End If
.MoveNext
Loop
.Close
End With

The emp variable= firstname_surname

Question, would i need to majorly moodify the code you have kindly supplied
me?
can I include the heft of both functions into my function with the while loop?

Cheers
 
It should be a simple change.

Instead of

strFile = Dir$(strFolder & "*.tft")

use

strFile = Dir$(strFolder & Emp & "*.tft")


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Songoku said:
Thanks for getting back to me douglas,

I should have mentioned this earlier, the filename which I previously
mentioned as being firstname_surname dd.mm.yy.tft I take the
firstname_surname from the database. ie I have a while loop code which looks
like below

With Test1
Do Until .EOF
If Not IsNull(![EMP_NAME]) And (![STATUS]) = "To Be Sent" And
(![7_DAYS_BEFORE]) = Date And (![MAN_Number]) = 12 Then
Emp = ![EMP_NAME]
NDate = ![7_DAYS_BEFORE] + 7
Manager = (![MANAGER])

strTo = "(e-mail address removed)"
strSubject = "data For: " & Emp & " Due On: " & NDate & " With "
& Manager & " Sent on : " & (![7_DAYS_BEFORE])
strBody = "Data Documents Attached"
FirstFile = "C:\test\Forms\data.zip"
SecondFile = "C:\test\Forms\" & Emp & ".tif"

SendNotesMail strTo, strSubject, strBody, FirstFile, SecondFile,
ThirdFile

End If
.MoveNext
Loop
.Close
End With

The emp variable= firstname_surname

Question, would i need to majorly moodify the code you have kindly supplied
me?
can I include the heft of both functions into my function with the while loop?

Cheers

Douglas J Steele said:
Something like the following untested aircode should work:

Function MostRecentFile(FolderName As String) As String

Dim dtmLatest As Date
DIm intFirstSpace As Integer
Dim strFile As String
Dim strFileDate As String
Dim strFolder As String
Dim strLatestFile As String

' Make sure there's a slash at the end
' of the folder name
If Right$(FolderName, 1) <> "\" Then
strFolder = FolderName & "\"
Else
strFolder = FolderName
End If

' Initialize variables
dtmLatest = #12/30/1899#
strLatestFile = vbNullString

strFile = Dir$(strFolder & "*.tft")
Do While Len(strFile) > 0
' Find the space between the name and the date
intFirstSpace = InStr(strFile, " ")
If intFirstSpace > 0 Then
strFileDate = Mid$(strFile, (intFirstSpace + 1), 8)
If CDate(strFileDate) > dtmLatest Then
dtmLatest = CDate(strFileDate)
strLatestFile = strFile
End If
End If
strFile = Dir$()
Loop

MostRecentFile = strLatestFile

End Function


Note that because your dates are in dd.mm.yy format, CDate will only work if
the user's regional settings have the Short Date set to that format (in
Regional Settings). For a more general approach, you might want to create
your own conversion function, such as:

Function Convert_ddmmyy(InputString As String) As Variant

Dim intDay As Integer
Dim intMonth As Integer
Dim intYear As Integer
Dim varReturn As Variant

varReturn = Null

If Len(InputString) = 8 Then
If Mid$(InputString, 3, 1) = "." And _
Mid$(InputString, 6, 1) = "." Then
intDay = CInt(Left$(InputString, 2))
intMonth = CInt(Mid$(InputString, 4, 2))
intYear = CInt(Right$(InputString, 2))
varReturn = DateSerial(intYear, intMonth, intDay)
End If
End If

Convert_ddmmyy = varReturn

End Function

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


need
is
Click to expand...
Click to expand...
 
I was not sure if you would get the question I ask you , about the question
you asked back in June.
Did you ever get an answer as to whether or not ths can be done. If I
understand you correctly, you want the change to take place when the query is
ran? I have a field that is for disputed items, when the Cm's run thier
daily queries if an item has been marked "Disputed" in the disputed field
then I need the record to change to red. I don't use reports just queries,
and I have a main form where the queries are listed.
 
Table/query level conditional formating is not available or possible.
However, if you use a form to display the results, it is possible to set
up Conditional Formating (right click on the field to highlight) to do
just that.
 
Back
Top