I have an application that stores values on 15 minute intervals. In one
function, I need to find the minimum average value over a 3 hour
period. So I make a query for each 3 hour period, compare it to the
last period, and save it if it has a lower value. The time is
tolerable, but pretty annoying (about 25 seconds). I want to know if
anyone has any suggestions for speeding up this process. My code is
below. Thanks!!


Do
EndAM = StartAM + #3:00:00 AM#

'Query with records during this 3 hour period
strQry = "SELECT INFLOW.FM_FLOW FROM inflow WHERE
(((Int([INFLOW]![FM_DATE]))=#" + Format(dtForm, "mm-dd-yy") + "#) AND
((INFLOW.FM_FMID)=" + strFMID + ") AND
((([INFLOW]![FM_DATE]-Int([INFLOW]![FM_DATE])))=#" + Format(StartAM,
"ttttt") + "# And (([INFLOW]![FM_DATE]-Int([INFLOW]![FM_DATE])))<#" +
Format(EndAM, "ttttt") + "#));"
If fQueryExist(qryName) Then db.QueryDefs.Delete qryName

Set qdfAM = db.CreateQueryDef(qryName, strQry)

'Find average value for this 3 hour period
tmpAvgAM = DAvg("fm_FLOW", qryName)
If AvgAM tmpAvgAM Then
AvgAM = tmpAvgAM
End If
tmpAvgAM = 0
StartAM = StartAM + IntervalAM
db.QueryDefs.Delete qdfAM.Name
Set qdfAM = Nothing

Loop While EndAM < LastAM

wrote:
I have an application that stores values on 15 minute intervals. In one
function, I need to find the minimum average value over a 3 hour
period. So I make a query for each 3 hour period, compare it to the
last period, and save it if it has a lower value. The time is
tolerable, but pretty annoying (about 25 seconds). I want to know if
anyone has any suggestions for speeding up this process. My code is
below. Thanks!!


Do
EndAM = StartAM + #3:00:00 AM#

'Query with records during this 3 hour period
strQry = "SELECT INFLOW.FM_FLOW FROM inflow WHERE
(((Int([INFLOW]![FM_DATE]))=#" + Format(dtForm, "mm-dd-yy") + "#) AND
((INFLOW.FM_FMID)=" + strFMID + ") AND
((([INFLOW]![FM_DATE]-Int([INFLOW]![FM_DATE])))=#" + Format(StartAM,
"ttttt") + "# And (([INFLOW]![FM_DATE]-Int([INFLOW]![FM_DATE])))<#" +
Format(EndAM, "ttttt") + "#));"
If fQueryExist(qryName) Then db.QueryDefs.Delete qryName

Set qdfAM = db.CreateQueryDef(qryName, strQry)

'Find average value for this 3 hour period
tmpAvgAM = DAvg("fm_FLOW", qryName)
If AvgAM tmpAvgAM Then
AvgAM = tmpAvgAM
End If
tmpAvgAM = 0
StartAM = StartAM + IntervalAM
db.QueryDefs.Delete qdfAM.Name
Set qdfAM = Nothing

Loop While EndAM < LastAM


Where is database? in ms access?
Don't build query dynamically,
write a query with paramaters and run it passing parameters only.

Yes, database is in ms access. With the parameters, the values come
from a form. I call the function from a couple different forms, so I
can't name the form specifically in the query. I also have at least 2
forms open. How can I pass the values to the query? Thanks!

witek wrote:
wrote:
I have an application that stores values on 15 minute intervals. In one
function, I need to find the minimum average value over a 3 hour
period. So I make a query for each 3 hour period, compare it to the
last period, and save it if it has a lower value. The time is
tolerable, but pretty annoying (about 25 seconds). I want to know if
anyone has any suggestions for speeding up this process. My code is
below. Thanks!!


Do
EndAM = StartAM + #3:00:00 AM#

'Query with records during this 3 hour period
strQry = "SELECT INFLOW.FM_FLOW FROM inflow WHERE
(((Int([INFLOW]![FM_DATE]))=#" + Format(dtForm, "mm-dd-yy") + "#) AND
((INFLOW.FM_FMID)=" + strFMID + ") AND
((([INFLOW]![FM_DATE]-Int([INFLOW]![FM_DATE])))=#" + Format(StartAM,
"ttttt") + "# And (([INFLOW]![FM_DATE]-Int([INFLOW]![FM_DATE])))<#" +
Format(EndAM, "ttttt") + "#));"
If fQueryExist(qryName) Then db.QueryDefs.Delete qryName

Set qdfAM = db.CreateQueryDef(qryName, strQry)

'Find average value for this 3 hour period
tmpAvgAM = DAvg("fm_FLOW", qryName)
If AvgAM tmpAvgAM Then
AvgAM = tmpAvgAM
End If
tmpAvgAM = 0
StartAM = StartAM + IntervalAM
db.QueryDefs.Delete qdfAM.Name
Set qdfAM = Nothing

Loop While EndAM < LastAM


Where is database? in ms access?
Don't build query dynamically,
write a query with paramaters and run it passing parameters only.