Criteria Parameter in query for two fields: help

  • Thread starter Thread starter Rx87
  • Start date Start date
R

Rx87

I have a form that utilizes two contact lists in separate combo boxes to
illustrate projects that multiple people have worked on.

I am trying to make it so that a report will illustrate the associated data
based on these contacts.

The selection will be based on either one contact (from either list) or
both. Whereby on selection, the report will generate the associated data
based on these user names.

How would I go about creating a parameter in the query so that it will limit
the content to reflect either contact list or a combination of the two?

I am a beginner to Access and would greatly appreciate any detailed
responses.
 
Open the query that you form is based

In the column that contains that details of the people (who you filter using
the 1st combo box on your form) add this to the criteria row

Forms![NameOfForm]![ComboName] Or Forms![NameOfForm]![ComboName] Is Null

Note change NameOfForm and ComboName to what they really are

Do the same in the criteria row of the other column (that contains details
of the 2nd group of people that want to filter the form on)

Save the query.

Next open the form in design view and add a button
Use the wizard if you want
In the code behind the button's OnClick put
Me.Requery

Save

Give it a try


*******************************

You don't really need this, but if you are interested
All the below may look a little complex but it's quite easy to do.
The sql of your query would look like this

I assume that
You have 2 tables called tblTable1 and tblTable2
Your have a form called frmMyForm
In frmMyForm you have 2 combos called cboMyCombo1 and cboMyCombo2
You have a query called qryMyQuery


SELECT tblTable1.SomeFieldInTbl1, tblTable2.SomeFieldInTbl2
FROM tblTable2 INNER JOIN tblTable1 ON tblTable2.SomeFieldInTbl2 =
tblTable1.CDClientID
WHERE (((tblTable1.SomeFieldInTbl1)=[Forms]![frmMyForm]![cboMyCombo1]) AND
((tblTable2.SomeFieldInTbl2)=[Forms]![frmMyForm]![cboMyCombo2])) OR
(((tblTable2.SomeFieldInTbl2)=[Forms]![frmMyForm]![cboMyCombo2]) AND
(([Forms]![frmMyForm]![cboMyCombo1]) Is Null)) OR
(((tblTable1.SomeFieldInTbl1)=[Forms]![frmMyForm]![cboMyCombo1]) AND
(([Forms]![frmMyForm]![cboMyCombo2]) Is Null)) OR
((([Forms]![frmMyForm]![cboMyCombo1]) Is Null) AND
(([Forms]![frmMyForm]![cboMyCombo2]) Is Null));
 
Back
Top