Criteria in Query causes it to be slow

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

Guest

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.
 
Do you suppose you could provide some more detail? What do the queries and
the function look like, for example?
 
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.
 
Back
Top