Macro Expressions for Report Selection Form

[Karen]

I have an unbound form with a list box containing the names of reports the
user can select. Once they select the report, there is a part of the form
where they can enter the criteria to filter the report. These criteria boxes
are unbound. The criteria are: Begin Date and End Date(text boxes), (for a
date range), Product (drop down), and CustomerID (text box). Each report has
its own query, which include these criteria fields. When they click on the
command button called Open Report, I want to pass the criteria to each query
using a macro.

I have the following: Open Report Report Name=[Forms]![View
Reports]![lstReports]. I don't know how to include all the parameters into
the Where statement for the action. Is this even possible?

 

[Steve Schapel]

Karen,

Yes, it is possible to write the criteria into the Where Condition
argument of the OpenReport action. You would put something like this:
([YourDate] Between [Forms]![View Reports]![Begin Date] And
[Forms]![View Reports]![End Date]) And ([ProductID]=[Forms]![View
Reports]![Product]) And ([CustomerID]=[Forms]![View Reports]![CustomerID])

However, I myself would normally prefer to write these criteria into the
queries that the reports are based on. So this does not involve the
macro. This involves going to the design view of the query that the
report is based on, and, for example, in the Criteria row of your date
field in the query design grid, put:
Between [Forms]![View Reports]![Begin Date] And [Forms]![View
Reports]![End Date]

 

[Karen]

The user may not enter criteria for all the fields. They may put in the data
range and product and leave the customer id blank. How would I write the
parameter into the query.

Karen,

Yes, it is possible to write the criteria into the Where Condition
argument of the OpenReport action. You would put something like this:
([YourDate] Between [Forms]![View Reports]![Begin Date] And
[Forms]![View Reports]![End Date]) And ([ProductID]=[Forms]![View
Reports]![Product]) And ([CustomerID]=[Forms]![View Reports]![CustomerID])

However, I myself would normally prefer to write these criteria into the
queries that the reports are based on. So this does not involve the
macro. This involves going to the design view of the query that the
report is based on, and, for example, in the Criteria row of your date
field in the query design grid, put:
Between [Forms]![View Reports]![Begin Date] And [Forms]![View
Reports]![End Date]

--
Steve Schapel, Microsoft Access MVP

I have an unbound form with a list box containing the names of reports the
user can select. Once they select the report, there is a part of the form
where they can enter the criteria to filter the report. These criteria boxes
are unbound. The criteria are: Begin Date and End Date(text boxes), (for a
date range), Product (drop down), and CustomerID (text box). Each report has
its own query, which include these criteria fields. When they click on the
command button called Open Report, I want to pass the criteria to each query
using a macro.

I have the following: Open Report Report Name=[Forms]![View
Reports]![lstReports]. I don't know how to include all the parameters into
the Where statement for the action. Is this even possible?

 

[Steve Schapel]

Karen,

Like this:

[CustomerID]=[Forms]![View Reports]![CustomerID] Or [Forms]![View
Reports]![CustomerID] Is Null

* Note that if you write the criteria into the query in this way, and
then save the query, the next time you open the query in design view,
you will see that Access has re-arranged the criteria to suit its own
purposes - don't worry about this, it will work fine.

Other than that, the way to handle this is to use VBA to build the Where
clause of the query in code.

 

[Steve Schapel]

Apologies, Karen, this was not correct, I was in too much of a rush...
If it's going into the query criteria, then it's just like this:

[Forms]![View Reports]![CustomerID] Or [Forms]![View
Reports]![CustomerID] Is Null

 

[Karen]

Thanks so much. This is great. I see what you mean about Access rearranging
the criteria. I had done a simpler version before, but never with multiple
criteria and the possibility of some of the users choices being null.

Apologies, Karen, this was not correct, I was in too much of a rush...
If it's going into the query criteria, then it's just like this:

[Forms]![View Reports]![CustomerID] Or [Forms]![View
Reports]![CustomerID] Is Null

--
Steve Schapel, Microsoft Access MVP

Karen,

Like this:

[CustomerID]=[Forms]![View Reports]![CustomerID] Or [Forms]![View
Reports]![CustomerID] Is Null

* Note that if you write the criteria into the query in this way, and
then save the query, the next time you open the query in design view,
you will see that Access has re-arranged the criteria to suit its own
purposes - don't worry about this, it will work fine.

Other than that, the way to handle this is to use VBA to build the Where
clause of the query in code.