criteria for query from form

[Daniel]

I have a form that has 7 fields and 3 check boxes, all unbound to enter
query criteria. It works fine, but my problem is I need to know if I can
only run one query based on any of the possible users selections from the
criteria form. OR, do I have to create a query for each of the
100,000,000,000 possible selections? Any suggestions/solutions would help
me greatly.

Thanks,

Daniel

 

[Steve Schapel]

Daniel,

I think there are at least 2 approaches you can take here, to create
your query...

I assume that each of the "fields" (presumably textboxes or
comboboxes) and checkboxes on the form relates to a field in the
query. So, the checkboxes are probably easy... just enter the
reference to the relevant checkbox in the criteria of the related
field, using syntax such as...
[Forms]![NameOfYourForm]![NameOfCheckbox]
As regards the other controls, I assume you want a criteria to be
applied if there is an entry in the control, and not if not. Correct?
Put this in the criteria of the relevant fields in the query...
Like Nz([Forms]![NameOfYourForm]![NameOfTextbox],"*")
Your requirements may be more complex than this, but the above will
give you a start.

The other way is to use vba code in the appropriate place in the
form's module to build the query. For a sketch example...
Dim strSQL As String
Dim NextOp As String
NextOperator = " WHERE "
strSQL = "SELECT * FROM YourTable"
If Not IsNull(Me.1stTextbox) Then
strSQL = strSQL & NextOp & "YourField ='" & Me.1stTextbox & "'"
NextOp = " And "
End If
If Not IsNull(Me.SecondTextbox) Then
...etc...

- Steve Schapel, Microsoft Access MVP