What Access is doing is to rerun queries into your original query, based on
the sorting and grouping that it must perform within the report.
Instead of including all those criteria in the original query when you may
only need to use a few of the 25 or so, could you leave them out of the
query? Instead, generate a WhereCondition for your OpenReport, based only on
the text boxes that have a value. This should dramatically reduce the number
of criteria that Access has to evaluate to get the report open, and it may
even facilitate the use of indexes (depending on the specifics).
The example below shows how to build up a WhereCondition string from only
the text boxes where the user entered a value. It is coded in such as way as
to make it easy to add as many conditions as you need.
---------------code starts------------------
Dim strWhere As String
Dim lngLen As Long
If Not IsNull(Me.txtFilterCity) Then 'Text field example.
strWhere = strWhere & "([City] = """ & Me.txtFilterCity & """) AND "
End If
If Not IsNull(Me.txtFilterAmount) Then 'Number field example
strWhere = strWhere & "([Amount] = " & Me.txtFilterAmount & ") AND "
End If
'etc for other boxes.
'Now cut off the trailing " AND ".
lngLen = Len(strWhere) - 5
If lngLen > 0 Then
strWhere = Left$(strWhere, lngLen)
End If
DoCmd.OpenReport "Report1", acViewPreview, , strWhere
---------------code ends------------------
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users -
http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Wayne" <cq*******@volcanomail.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...
I am running a complex query using about 25 criteria that are entered
on a query form. If any individual criteria isn't required its field is
left as "*" on the form. When I run the query the first time the
results take about 3 or 4 seconds to appear on a "Query Results" form.
Subsequent queries using either the same or different criteria give a
result in less than 1 second.
The "Query Results" form has a button to open an equally complex report
(about 30 fields) based on the same query as the form. The report takes
about 20 seconds to open in print preview each time. What is Access
actually doing when a report is being generated and why will the query
results display on a form so much more quickly than on a report?
Is there anything I can do, other than simplify the query, to reduce
the time that the report takes to open?
Thanks in advance.
