Dynamic Querying for a Report

[pedroghuk]

Good afternoon

I have had an unsuccessful search of this Forum for an answer …

Can someone please assist me?

I have a simple Form with a series of bounds fields on. These all relate to Table A.

These fields are:

Date Referred {which may be unnecessary?}
Combo Box B which has values 1, 2 and 3
Combo Box C which has values 4 and 5
Combo Box D which has 6, 7, 8, 9 and 10.

Ideally, I want the User to select options from the three Combo Boxes B, C and D, e.g., values 2, 5 and 9; press a button which will prompt for a Date Referred Start and Date Referred End date and produce a Report showing filtered values 2, 5 and 9 for the Date Referred period.

What is the syntax for this dynamic reporting Query?

What would happen if one or more of the Combo Boxes were left blank, i.e. no value selected?

Regards and thank you for any help received.

 

[Mr. B]

You can add a reference to a control on your form in the criteria row of each column in your query on which you need to filter that will then cause the query to be filtered by the value selected in the controls on the form. Try using the Expression Builder to set the reference to each control on your form.

When you use this type of filtering of the query users must select a value from each of the specific controls on your form. If any control is left without a selection the query will not return any values.

To do the filtering you want and be able to let the user leave a selection blank, you would need to use VBA code to evaluate each selection and build the sql statement for your query based on the users selections. Once the sql statement has been constructed in VBA code, that sql statement can be assigned as the Row Source for the Query Definition.

If you are not familiar or comfortable with VBA code, try the first option and just require that your users select a value from each of the available controls.