Filter Report by form

[Doug]

I want to set a filter by calling a form from the On Open Event of a Report.
On the form I have 3 fields: Start Date, End Date, and Vendor. The Report is
based on a query that has DatePaid (date field) and VendorName (txt field).
How can I set the filter in code for a report so that only the DatePaid
between Start Date and End Date appear and where Vendor = Vendor Name?

 

[Allen Browne]

Set the Filter property of the report.

There's an example of how to do that in:
Limiting a Report to a Date Range
at:
http://allenbrowne.com/casu-08.html
The example actually works the other way around (i.e. you open the form,
enter the criteria, and then click a button to open the report.)

If you need an example of how to build the filter string based on many
possible filter fields, see:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html
Again, this example shows the results in the search form, but the code is
identical for a report. You end up using strWhere as the WhereCondition for
OpenReport.

 

[fredg]

I want to set a filter by calling a form from the On Open Event of a Report.
On the form I have 3 fields: Start Date, End Date, and Vendor. The Report is
based on a query that has DatePaid (date field) and VendorName (txt field).
How can I set the filter in code for a report so that only the DatePaid
between Start Date and End Date appear and where Vendor = Vendor Name?

You don't want to use the VendorName field. Use the VendorID field.
Several Vendors might have the same name, but the VendorID should be
unique.

Create an unbound form.
Add a combo box.
Set the Row Source of the combo box to include the
VendorID field and the Vendor Name.
Name the Combo Box 'FindVendor'.
Set it's Bound column to 1.
Set the Column Width property to 0";1"

Add 2 unbound text controls.
Set their Format property to a valid date format.
Name one control 'StartDate'. Name the other control 'EndDate'.

Add a Command Button to the form.
Code the button's click event:

Me.Visible = False

Name this form 'ParamForm'.

In the Report's Record Source (the Query) [VendorID] field criteria
line write:
forms!ParamForm!FindVendor

On the [DatePaid] criteria line, write:
Between forms!ParamForm!StartDate and forms!ParamForm!EndDate

Next, code the report's Open event:
DoCmd.OpenForm "ParamForm", , , , , acDialog

Code the report's Close event:
DoCmd.Close acForm, "ParamForm"

When ready to run the report, open the report.
The form will open and wait for the selection of the Vendor and entry
of the dates.
Click the command button and then report will run.
When the report closes, it will close the form.

 

[Doug]

Your link is excellent...exactly what I needed! It will save me hours of
trying to build a proper search string.

 

[Doug]

This approach will work well... I can test for values in ParamForm and change
the recordsource of the report in the On Open event to something that returns
all values if the user does not wish to set a filter.

I want to set a filter by calling a form from the On Open Event of a Report.
On the form I have 3 fields: Start Date, End Date, and Vendor. The Report is
based on a query that has DatePaid (date field) and VendorName (txt field).
How can I set the filter in code for a report so that only the DatePaid
between Start Date and End Date appear and where Vendor = Vendor Name?

You don't want to use the VendorName field. Use the VendorID field.
Several Vendors might have the same name, but the VendorID should be
unique.

Create an unbound form.
Add a combo box.
Set the Row Source of the combo box to include the
VendorID field and the Vendor Name.
Name the Combo Box 'FindVendor'.
Set it's Bound column to 1.
Set the Column Width property to 0";1"

Add 2 unbound text controls.
Set their Format property to a valid date format.
Name one control 'StartDate'. Name the other control 'EndDate'.

Add a Command Button to the form.
Code the button's click event:

Me.Visible = False

Name this form 'ParamForm'.

In the Report's Record Source (the Query) [VendorID] field criteria
line write:
forms!ParamForm!FindVendor

On the [DatePaid] criteria line, write:
Between forms!ParamForm!StartDate and forms!ParamForm!EndDate

Next, code the report's Open event:
DoCmd.OpenForm "ParamForm", , , , , acDialog

Code the report's Close event:
DoCmd.Close acForm, "ParamForm"

When ready to run the report, open the report.
The form will open and wait for the selection of the Vendor and entry
of the dates.
Click the command button and then report will run.
When the report closes, it will close the form.