FileSearch LastModified Type mismatch

CevinMoses

New member
Local time
Today, 03:10
Joined
Sep 17, 2010
Messages
6
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
 
I think you need some more arguments with the Execute action.

.Execute msoSortByLastModified, msoSortOrderAscending

By the way, I don't see any With block in your code?
 
Sorry, I tried to streamline it and missed the "With" before the "Application.FileSearch".

The code stops running at the .LastModified line, before it ever gets to the .Execute line.

What I don't know is why the .LastModified line won't accept a variable for its input (".LastModified = stDateFilter"). If I run it hard coded in (".LastModified = msoLastModifiedToday"), everything runs just fine.

-Cevin
 
That's a vb constant which (I think) it's equivalent is an Integer. Try converting it to Integer instead. CInt() or just make your variable declaration Integer and trap for Nulls in the DLookup()
 
Solved it. 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
 
New problem.

We are migrating to Access 2010, primarily because of how easy it is to export pdf files of everything we print, but now the Application.FileSearch line returns a run-time error '2455'. From what I can tell, it is no longer supported with Access 2010, but I am having trouble finding a new method of listing the files in a certain directory that meet a certain requirement (date and .txt extension).

Does anyone know what the replacement is or a new way to do it?

Thanks,
Cevin
 

Users who are viewing this thread

Back
Top Bottom