Optional Criteria in Query

B

b dubbin

ok - so I've got 6 fields on my form. I want the user to be able to enter
information in as many or as few fields as he wants and then run a query
based only on the fields that have data in them. If my math is correct there
are 63 different combinations available and I don't want to make a massive IF
statement in VBA!

How do I create a query that will, depending on the fields chosen, use those
fields (and their corresponding tables and relationships) as criteria? BUT
if the field is not chosen then to eliminate the corresponding table from the
query. It's almost like I want to build the query on the fly depending on
what fields are selected.

Any help would be great!
 
C

Chaim

You have to do this in VB. Loop through the controls on the form and if
there is nothing in the control simply continue the loop. If there is
something in the control, concatenate the appropriate criteria expression to
a string in which you are holding the conditions that are needed.

Good Luck!
 
Top