Simplifying a WHERE clause

[cinnie]

greetings

I have a form with 3 comboboxes: cboZone, cboRegion and cboDivision. When
the user clicks a button on the form, a query is run. The WHERE clause of
the query starts like this:

WHERE (
(S.Zone = Forms!frmReport.cboZone)
And (S.Region = Forms!frmReport.cboRegion)
And (S.Division = Forms!frmReport.cboDivision))
OR etc...

Using abbreviations for simplicity, the entire WHERE clause is:

WHERE (Z=cboZ AND R=cboR AND D=cboD) '0 nulls
OR (Z=cboZ AND R=cboR AND D is Null) 'exactly 1 null
OR (Z=cboZ AND R is Null AND D=cboD)
OR (Z is Null AND R=cboR AND D=cboD)
OR (Z=cboZ AND R is Null AND D is Null) 'exactly 2 nulls
OR (Z is Null AND R=cboR AND D is Null)
OR (Z is Null AND R is Null AND D=cboD)
OR (Z is Null AND R is Null AND D is Null) '3 nulls

The SQL works fine, but now I need to add another combobox to the form,
cboArea. This means that the WHERE clause will have 16 parts instead of 8.

Here is my question. Is there a simpler or more effecient way to write this
WHERE clause that will produce the same result? I need it to cover the cases
where none, exactly 1, exactly 2, exactly 3, and all 4 of the cbos are null.

much thanks for any clues
Cinnie

 

[John W. Vinson]

greetings

I have a form with 3 comboboxes: cboZone, cboRegion and cboDivision. When
the user clicks a button on the form, a query is run. The WHERE clause of
the query starts like this:

WHERE (
(S.Zone = Forms!frmReport.cboZone)
And (S.Region = Forms!frmReport.cboRegion)
And (S.Division = Forms!frmReport.cboDivision))
OR etc...

Using abbreviations for simplicity, the entire WHERE clause is:

WHERE (Z=cboZ AND R=cboR AND D=cboD) '0 nulls
OR (Z=cboZ AND R=cboR AND D is Null) 'exactly 1 null
OR (Z=cboZ AND R is Null AND D=cboD)
OR (Z is Null AND R=cboR AND D=cboD)
OR (Z=cboZ AND R is Null AND D is Null) 'exactly 2 nulls
OR (Z is Null AND R=cboR AND D is Null)
OR (Z is Null AND R is Null AND D=cboD)
OR (Z is Null AND R is Null AND D is Null) '3 nulls

The SQL works fine, but now I need to add another combobox to the form,
cboArea. This means that the WHERE clause will have 16 parts instead of 8.

Here is my question. Is there a simpler or more effecient way to write this
WHERE clause that will produce the same result? I need it to cover the cases
where none, exactly 1, exactly 2, exactly 3, and all 4 of the cbos are null.

much thanks for any clues
Cinnie

Use the NZ() function:

WHERE NZ([z], [cboZ]) = cboZ
AND NZ([R], [cboR]) = cboR
AND NZ([D], [cboD]) = cboD

 

[Albert D. Kallal]

If I'm reading this correct, your problem is is your try to work out all the
possible combinations, and the simple solution is to process each control
one at a time and "built up" the condistions:

dim strwhere as string

If isnull(me.cboZone) = false then
strWhere = "(s.Zone = " & me.cobZone & ")"
end if

if isnull(me.cboRegion) = false then
if strWhere <> "" then
strWhere = strwhere & " and "
end if
strWhere = strWhere & "(SRegion = " & me.cboRegion & ")"
end if

if isnull(me.cboDivision) then
if strWhere <> "" then
strWhere = strWhere & " and "
end if
strWhere = strWhere & "(SRegion = " & me.cboDivision & ")"
end if

docmd.OpenReport "frmSales",acViewPreview,,strWhere

Notice how in the above you're free to add as many new conditions as you
want over time.

Also the above code assumes that of a combobox is left blank, then you want
"all". If in fact that when a combobox is left blank, that the criteria for
the combobox must be null, the use:

strWhere = strWhere & " and "
if isnull(me.cboDivision) then
strWhere = strWhere & "(SRegion = " & me.cboDivision & ")"
else
strWhere = strWhere & "(SRegion is Null)"
end if

In the above you don't have to test if Starr well it is empty already,
because you're always going to have at least a condition from the previous
value. However if leaving a combobox like means in fact all are any values,
then use the first format, and for each combobox you add, you will need a
condition to test if the where clause ARI has some conditions in it, and
then you have to append the " and " as the above example code shows.

The above approach means that you're simply cumulative the adding that
conditions, not trying to build a straying that's based on the combinations
and permutations of the three variables, because if you go to five, (5!)
factorial = 120 different values you have to test. The above solves this
without this issue.

 

[cinnie]

Thank you to Albert and John for some great insights!