using DIR to get a max file

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Songoku - 20 Sep 2005 15:04 GMT

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

Reply to this Message

Douglas J Steele - 20 Sep 2005 16:46 GMT

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

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> Hi,
>
[quoted text clipped - 5 lines]
>
> code pointers appreciated

Reply to this Message

Songoku - 21 Sep 2005 16:28 GMT

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 = "my.email@yahoo.com"
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

> Something like the following untested aircode should work:
>
[quoted text clipped - 76 lines]
> >
> > code pointers appreciated

Reply to this Message

Douglas J Steele - 21 Sep 2005 17:22 GMT

It should be a simple change.

Instead of

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

use

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

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> Thanks for getting back to me douglas,
>
[quoted text clipped - 115 lines]
> > >
> > > code pointers appreciated

Reply to this Message

tamxwell - 30 Sep 2005 20:37 GMT

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.

> now where were we.....ah yes any recommendations as to how to achieve this?

> Hi,
>
[quoted text clipped - 5 lines]
>
> code pointers appreciated

Reply to this Message

David C. Holley - 01 Oct 2005 00:04 GMT

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.

> I was not sure if you would get the question I ask you , about the question
> you asked back in June.
[quoted text clipped - 16 lines]
>>
>>code pointers appreciated

Reply to this Message