Multiple Selection from 4 Form fields

[JanGray]

I've created a query that pulls parameters from a form and I need the user to be able to query by 1, 2, 3, or 4 separate criteria. Right now, the query seems to work fine with either 3 or 4 criteria selected but it doesn't work if fewer than 3 criteria are selected -- i.e., 2 or 3 of the combo boxes are left blank. How can I improve this query to make this work?

Here's my query:



SELECT tblDeliverables.ID, tblDeliverables.[Department/Owner], tblDeliverables.[Deliverable name], tblDeliverables.[Deliverable format], tblDeliverables.[Targeted roles], tblDeliverables.[Referenced products], tblDeliverables.[Clients/Components]
FROM tblDeliverableFormat INNER JOIN (tblXPProducts INNER JOIN (tblXPClientsComponents INNER JOIN ((tblDeliverables INNER JOIN tblDepartments ON tblDeliverables.[Department/Owner] = tblDepartments.[Dept Code]) INNER JOIN tblPersonas ON tblDeliverables.[Targeted roles].Value = tblPersonas.[Targeted Role].Value) ON tblXPClientsComponents.Code = tblDeliverables.[Clients/Components].Value) ON (tblXPProducts.[Product Name] = tblXPClientsComponents.[XP Product(s)].Value) AND (tblXPProducts.[Product Code] = tblDeliverables.[Referenced products].Value)) ON tblDeliverableFormat.ID = tblDeliverables.[Deliverable format].Value
WHERE (((tblDeliverables.[Department/Owner])=[Forms]![frm1TESTMultiSearchOptions]![cboSearchDept]) AND ((tblDeliverables.[Clients/Components].Value)=[Forms]![frm1TESTMultiSearchOptions]![cboClient]) AND ((tblDeliverables.[Targeted roles].Value)=[Forms]![frm1TESTMultiSearchOptions]![cboPersona]) AND ((tblDeliverables.[Referenced products].Value)=[Forms]![frm1TESTMultiSearchOptions]![cboProduct])) OR (((tblDeliverables.[Department/Owner])=[Forms]![frm1TESTMultiSearchOptions]![cboSearchDept]) AND ((tblDeliverables.[Targeted roles].Value)=[Forms]![frm1TESTMultiSearchOptions]![cboPersona]) AND ((tblDeliverables.[Referenced products].Value)=[Forms]![frm1TESTMultiSearchOptions]![cboProduct])) OR (((tblDeliverables.[Department/Owner])=[Forms]![frm1TESTMultiSearchOptions]![cboSearchDept]) AND ((tblDeliverables.[Clients/Components].Value)=[Forms]![frm1TESTMultiSearchOptions]![cboClient]) AND ((tblDeliverables.[Targeted roles].Value)=[Forms]![frm1TESTMultiSearchOptions]![cboPersona])) OR (((tblDeliverables.[Department/Owner])=[Forms]![frm1TESTMultiSearchOptions]![cboSearchDept]) AND ((tblDeliverables.[Clients/Components].Value)=[Forms]![frm1TESTMultiSearchOptions]![cboClient]) AND ((tblDeliverables.[Referenced products].Value)=[Forms]![frm1TESTMultiSearchOptions]![cboProduct])) OR (((tblDeliverables.[Clients/Components].Value)=[Forms]![frm1TESTMultiSearchOptions]![cboClient]) AND ((tblDeliverables.[Targeted roles].Value)=[Forms]![frm1TESTMultiSearchOptions]![cboPersona]) AND ((tblDeliverables.[Referenced products].Value)=[Forms]![frm1TESTMultiSearchOptions]![cboProduct]));

[ranman256]

make a query that shows all records.
in a continuous form ,you have different controls to filter, when user clicks FIND button.

build the 'where' clause by cycling thru all the controls....
it executes after a find button CLICK event
if null, ignore.
if not, apply.

'----------------
sub btnFilter_click()
'----------------
dim sWhere as string 


sWhere = "1=1"
if not IsNUll(cboST) then sWhere = sWhere & " and [State]='" & cboST & "'"
if not IsNUll(cboCity) then sWhere = sWhere & " and [city]='" & cboCity & "'"
if not IsNUll(cboZip) then sWhere = sWhere & " and [ZipCode]='" & cboZip & "'"


If sWhere = "1=1" Then
  Me.FilterOn = False
Else
  Me.Filter = sWhere
  Me.FilterOn = True
End If
end sub

[Micron]

Change your AND operators that refer to the form controls to OR
or
write the sql in code by piecing together only the parameters that have been provided.

[ssanfu]

Also see Use a parameter to return all records if Null
Not my fav option but worth knowing about.