Criteria in Query causes it to be slow

[Scott J.]

I have a query that contains one field which calls a function. The function
takes the date and a machine and runs another query for just that machine to
find the difference in the date provided and the previous date. For Example:

Query1: Machine = 100 Date = 6/1/05

Function creates a Query2 for only Machine = 100 and all dates before
6/1/05. It finds the difference between 6/1/05 and the second record and
displays in Query2.

This runs fine, except I also have to add the criteria "<10" to Query1 for
that field. When I do this, the Query takes 1-2 minutes to run instead of a
few seconds. My workaround was to leave off the criteria and append Query1
to a table. Then run a query with that criteria from the new table. This
seems like a lot of excess work, however. Is there any better way?

Scott J.

 

[Chaim]

Do you suppose you could provide some more detail? What do the queries and
the function look like, for example?

 

[Scott J.]

No Problem. Here is the Function (The SQL statement is what I was referring
to as Query2):

Function GetDateDiff(Machine As Variant, daDate As Date)
On Error Resume Next
Dim MyDB As Database, MyRec As Recordset, MyLargeDate As Variant
Set MyDB = CodeDb
Set MyRec = MyDB.OpenRecordset("SELECT
OperatorPMandOilChangeTable.MachineNumber,
OperatorPMandOilChangeTable.ServiceDate FROM OperatorPMandOilChangeTable
Where [MachineNumber] = """ & Machine & """ AND [ServiceDate] <= #" & daDate
& "# ORDER BY OperatorPMandOilChangeTable.MachineNumber,
OperatorPMandOilChangeTable.ServiceDate DESC")
If Not MyRec.EOF Then
MyLargeDate = MyRec!ServiceDate
MyRec.MoveNext
GetDateDiff = DateDiff("d", MyRec!ServiceDate, MyLargeDate)
End If
End Function

Query1 has the following fields and criteria:
MachineNumber; RecordNumber; ServiceDate (Criteria references two controls
on a form); WeeklyPMCheck (Criteria: True); Late:
GetDateDiff([MachineNumber],[ServiceDate]) (Criteria: <=10)

Does that help??

Scott J.