Display report based on selected Customer

[mandyric]

Hello
I have made a query which I need to display the records and dates in a
report for a
nominated customer.
The fields I used in the query are
CustomerID (Combo box), unitno, repairdate (parameter between start
date and end date) which come from a table called Quotation.
When I open my report the parameter asks for the start and end date
which is fine, but it shows all the customers from the CustomerID for
the dates chosen on the report.
How do I enter a criteria to select a customer first, then enter the
dates.
Any help would be greatly appreciated
Peter

 

[Allen Browne]

The parameter boxes popped up by the query cannot use a combo or listbox.

Leave the criteria out of the query. Instead, create a form where the user
selects the limiting dates and the customer from the drop-down list. You can
then build the WhereCondition to use with OpenReport so that the report is
limited to the dates and customer in the form.

For a basic example with just the dates, see:
Limiting a Report to a Date Range
at:
http://allenbrowne.com/casu-08.html

For a more comprehensive example of how to build the Where string from any
combination of non-blank boxes using different field types, download this
example for Access 2000 and later:
http://allenbrowne.com/unlinked/Search2000.zip
The example actually applies the filter to the form, but you do exactly the
same thing to create a WhereCondition for OpenReport.

 

[mandyric]

Allen,
Thank you for the Search 2000 zip. As I am learning as I go, I am
having trouble trying to understand how to implement my information
based on your sample.
Basically if you based an example of the report I need, it would come
from the Microsoft Orders DB.

From Orders Table the fields would be

CustomerID - Lookup Combo Box
PurchaseOrderNumber - Text
OrderDate - Date/Time
Have you a sample that suits
Thanks in advance
Peter

 

[Allen Browne]

I don't have a sample based on each of the MS templates, but the techniques
are the same.

Presumably you have an unbound combo, where the user selectes one customer
(or leaves it blank.) The bound column is probably a number, so test if the
combo Is Null, and if not, start the Where string using the Number example.

Your next field is Text type, so use the Text type example with the
double-quotes as delimiters around the value.

Your OrderDate is a date, so use the Date type example.

Once you have built up the string from the non-blank boxes, you can use
exactly the same code to chop off the trailing " AND ".

Then use the where string in the WhereCondition of OpenReport:
DoCmd.OpenReport "Report1", acViewPreview, , strWhere

 

[mandyric]

Allen,
thank you very much, doing those changes worked great. The only thing,
it seems to take longer to display the report
Thank again
Peter

 

[mandyric]

Allen,
thank you very much, doing those changes worked great. The only thing,
it seems to take longer to display the report
Thank again
Peter

 

[Allen Browne]

There are many, many factors that affect how long it takes to display the
report.

First thing might be to open your table in design view, and for each field
that you will regularly use for critieria or setting, set its Indexed
property to Yes (in the lower pane of table design.) Access will then be
able to use the index to select the records, instead of having to scan the
entire table.

If you are using the Like operator, try to use the wildcard only at the end,
e.g.:
Surname Like "Jon*"
A leading wildcard will prevent the use of the index.

For related tables, filter on the foreign key if possible (e.g. use a combo
with the bound field zero-width). That will be faster, and it might even
permit you to leave the lookup table out of the query which simplifies it
even further.

Don't bother doing any sorting in the query that feeds the report. The
sorting will be ignored, and determined by the Sorting And Grouping in the
report anyway.

For a Total query, you might speed some things up by using First rather than
Group By on all the fields that come from a table where you are already
grouping on its primary key.

If possible, avoid code in the report's events. This *really* slows the
report down. For example, if you can do the job with Conditional Formatting
rather than VBA code, the report might execute 2 or 3 times faster. (If
possible, set the report's HasModule property to No.)

In recent versions of JET, queries also run significantly slower if you use
user-defined function calls, so you might want to avoid that.

For general suggestions on how to get Access performing well, see Tony
Toews' "Access Performance FAQ" at:
http://www.granite.ab.ca/access/performancefaq.htm

In summary, the the technique we have discussed in this thread should not
slow the report down at all. If anything, they should expedite it, since we
are avoiding all the unnecessary and convoluted conditions in the WHERE
clause of the SQL statement, such as:
WHERE (([Forms].[Form1].[Text0] Is Null) OR ([SomeField] Like
[Forms].[Form1].[Text0]))