FileSearch LastModified Type Mismatch

[CevinMoses]

Using MS-Access 2000, I have a form which searches a specified directory for text files (hyphen delineated) which are then imported into a table so that order information can be extracted.



Dim stFileName As String
Dim stDateFilter As String
Dim Folder As String

stDateFilter = DLookup("[msoDateFilter]", "[tMsoDateFilter]", "[DateFilter] = '" & Me.DateFilter & "'")

Application.FileSearch
.NewSearch
stFileName = Nz(Me.TxtFilter, "") & "*." & Nz(Me.TxtExtension, "") & "*"
.FileName = stFileName
.LastModified = stDateFilter
.LookIn = Folder
.Execute

I want to narrow the search according to a time frame, and .LastModified works fine if I hard code it in, like "msoLastModifiedToday", but when I tried to use a variable, using the DLookup above, the variable comes up with the correct value (stDateFilter = "msoLastModifiedToday") but I get a "Run-time Error 13, Type mismatch" at that line.

Is there a problem with using a variable for that line, or am I doing something wrong? I tried running stDateFilter as a Variant, but that had no effect.

-Cevin

[weekend00]

I 've never use file search but I am quite sure LastModified is a date/time type. But your stDateFilter is a string. So, "type mismatch".

[CevinMoses]

That would make sense, but when you type in ".LastModified = ", VB automatically gives you the following options:

.LastModified = msoLastModifiedAnyTime
.LastModified = msoLastModifiedLastMonth
.LastModified = msoLastModifiedLastWeek
.LastModified = msoLastModifiedToday
.LastModified = msoLastModifiedThisMonth
.LastModified = msoLastModifiedThisWeek
.LastModified = msoLastModifiedYesterday

So it should accept the code, unless the mso... actually is a system variable for a date manipulation which sets the range. I'll try that and repost the results.

-Cevin

[CevinMoses]

Solved it, thanks for the tip. Each constant ("msoLastModified...") can be replaced by its numeric equivalent, and therefore changed based on the combo box like I described. By changing the hard coding and running a line that said:

Debug.Print "LastModified = " & .LastModified

I was able to figure out the numeric equivalent for each one. So, my new table for the DLookUp looks like this:

DateFilter msoDateFilter
All 7
Last Month 5
Last Week 3
This Month 4
This Week 6
Today 2
Yesterday 1

I also changed stDateFilter to be an integer. Then it works fine. Hope this helps someone else.

-Cevin