Question about passing dates

[ddikeht]

This should be a simple question for the pro's. I have a simple query that gets info from a table. If I hardcode the date in the query ie; between #01/01/2007# and #01/31/2007# the results are returned quickly, maybe 5 seconds or so.

However, if I get the date range from a form ie; Between [forms]![frmMain]![Date1] And [forms]![frmMain]![Date2] the same query takes upwards of 5 mins to run!

Can anyone tell me why this is? I have searched, but was unable to find anything on this topic. If you dont want to answer, could you at least give me some keywords to search for this topic?

Thanks!

 

[rainman89]

quick question, what is the name of the date field that you are querying?

 

[ddikeht]

The date field is called REPORTING_PERIOD_DT and is in a typical date format if that helps.

 

[CraigDolphin]

Weird.

Have you explicitly declared the parameters in your query? If not, try doing that and see if it speeds things up.

You could also try using

Between Eval("[forms]![frmMain]![Date1]") And Eval("[forms]![frmMain]![Date2]")

And see if that helps.

My guess is that your db is split and the backend is having to request the form values from the front end for each record and network speeds may be slowing things down.

 

[ddikeht]

Thanks for the help, tried both suggestions, but the results were the same. Any other thoughts?

 

[CraigDolphin]

Not really. Maybe if you post a zipped version that demonstrates the problem someone can figure out what's going on.

 

[Simon_MT]

Are your dates indexed?

Simon

 

[ddikeht]

I believe they are, but I would not know how to check. All data is coming from a massive data warehouse.