I've noticed that when i run my queries using a form (really a textbox) as a date range, my queries take forever to run whereas before using the form, it would take literally seconds. Any idea to speed this up? Thanks in advance.
I've noticed that when i run my queries using a form (really a textbox) as a date range, my queries take forever to run whereas before using the form, it would take literally seconds. Any idea to speed this up? Thanks in advance.
You need to provide more details otherwise anyone answering is just guessing
What type of query? How many fields / records?
Are you running the query from the form or its SQL equivalent?
Suggest you include your query SQL
From what you've said so far, I would suggest using two textboxes for the start & end dates of your date range
Apologies for the late reply and unclear example. I have quite a bit of queries, but the one I was working specifically on the form at the moment has 20 fields, 6 rows of criteria utilized in 5 of the fields. 2 of the fields contain simple expressions while another 2 contain more in-depth expressions. The query is a regular select query and is currently being ran in a subform on the form. When run from the form, I am using the code:. This query run through SQL runs in seconds, but as soon as I run it through the form using the textbox input as the date ranges, the runtime is considerably slower. To the point of inconvenience and a waste of time even using the form (although this isn't my goal/hopes).Code:me.subform.sourceobject = "Query." & "Query Name"
So I found where the problem is lying. For the query run through my form, the date filter is:
This runs perfectly and fast, but as soon as I change it to:Code:>=[Forms]![Invoices]![fromdate] And >=[Forms]![Invoices]![todate]
It begins to take ages!Code:IIf([Forms]![Invoices]![yestsel]=True,Date()-1,>=[Forms]![Invoices]![fromdate] And >=[Forms]![Invoices]![todate])
I'm surprised it completes at all. First of all what is the point of a date range >=from date and >=todateSo I found where the problem is lying. For the query run through my form, the date filter is:
This runs perfectly and fast, but as soon as I change it to:Code:>=[Forms]![Invoices]![fromdate] And >=[Forms]![Invoices]![todate]
It begins to take ages!Code:IIf([Forms]![Invoices]![yestsel]=True,Date()-1,>=[Forms]![Invoices]![fromdate] And >=[Forms]![Invoices]![todate])
It should be <=todate.
Now consider your iif statement.
If the condition is true it will enter yesterday's date which is fine.
However what do you want it to enter when the condition is false?
Using any date range here is wrong
I used a simple workaround for the date ranges and in regards to the >=todate, that was just a typo on my part. I understand what that would be doing. The way it should all work is if the checkbox yesterday is checked, the query will be ran for yesterday, but if left unchecked, the query will be run through the fromdate and todate specified by the user. The workaround I used was if yesterday is checked, it will enter yesterdays date in the fromdate and todate, but if left unchecked then the user is able to set the date range. So in the end, the queries logdate is run only throughrather than the iif statement.Code:>=[Forms]![Invoices]![fromdate] And <=[Forms]![Invoices]![todate]