Report info via form

[Denny]

Hello, I have created a form that will pull data and
create a report based on the form. The user wants three
primary fields of data to be pulled and used to create
the report data.

1) Vendor name - combo box
2) Year
3) Quarter

I am able to get the report to be created by selecting
either the year or quarter, but not with the Vendor name
list from the combo box. Complicating matters more is
that the user wishes to be able to create the report
using ANY combination of the fields above. (ie. Vendor
name and year, year or quarter, year and quarter, year
only)

Any help would be appreciated greatly. Thanks.

Denny French

 

[Larry Linson]

I know there's no limit of two on the items that can be used in the WHERE
clause of a Query, one of the two most common methods used to limit the data
printed in a Report based on information entered in a Form. I can't be sure,
but believe I have also used more than two criteria in the WhereCondition
argument of the DoCmd.OpenReport statement, the other common method.

Check the result of your VBA code that builds either the WHERE clause or the
"WHERE clause without a WHERE" that is used in the WhereCondition argument
by displaying it in a MsgBox or using Debug.Print to write it to the
Immediate/Debug window.

If you need to use the Query or SQL statement itself (I find this more
efficient when reporting against very large RecordSources), save it in an
invisible Control on the Form, and use it to replace the Report's
RecordSource in the Open event of the Report. You can use a Public variable
in a standard Module, as an alternative, or, ONLY if you're using Access
2002, the OpenArgs argument of the DoCmd.OpenReport .

Larry Linson
Microsoft Access MVP