MS Access date range functions slowing down query

Question

I have a query in which I am using a between statement to pull data for a date range. I have a VBA function built, that will evaluate the last date (Max Date) in a table (to get the 1st date for the between) and another function to evaluate the stop date (last date of the between)...So my between date statement looks like this: Between hhMaxDte() And hhLastDte()

For some reason having these 2 functions in my between statement takes what seems to be 8x longer to run the query than if I just manually put the actual dates in myself. Anyone out there know why this is and how I can dynamically do a similar process, but take the same amount of time to run the query, as when I manually enter the dates? My functions code is below:

Function hhMaxDte()

If DMax("row_date", "HH_CIB_Raw_Data") + 1 = Date Then
    hhMaxDte = 0
Else
    hhMaxDte = DMax("row_date", "HH_CIB_Raw_Data") + 1
End If

End Function

Function hhLastDte()

If DMax("row_date", "HH_CIB_Raw_Data") + 1 = Date Then
    hhLastDte = 0
Else
    hhLastDte = Date - 1
End If

End Function

Based on your description I am guessing those two functions run for every single record, even though they don't have to since they do not use any record specific parameters. — SunKnight0
Yep, I just stumbled upon exactly what you're saying here: allenbrowne.com/QueryPerfIssue.html — wlfente
The functions will be called once only as they don't have any parameters, so if you adjust your query replacing the functions with fixed values, you'll see pretty much the same result. However, without your query, you leave us guessing in the wild. It could be that you miss an index on the date field. — Gustav

wlfente wlfente · Accepted Answer · 2015-11-04T18:02:22

I've figured out the answer based off of the feedback in the comments above. The function was running for each row of data, thus significantly slowing down the return of the results. In order to avoid this, I used the built in function Dmax (to analyze the max indexed date in a table) and Date to create a between statement in the Access query criteria section:

Between DMax("row_date","HH_CIB_Raw_Data")+0 And Date()-1

Now the append query is able to dynamically pull data based off of the recent dates missing in the table and runs in a matter of seconds VS 2-3 min. Thanks for all the input guys!!

MS Access date range functions slowing down query

1 Answers