[RESOLVED] Where in sql only if not equal to <all>

[rack]

How would I perform a Where, in a SQL command, only if the item is not equal to "<All>"

Currently I have

WHERE Q4.AgentGroup= [Forms]![frmCDErrorReports]![Group]

But, the first item in the dropdown [group] is "<All>"

Is there a way to structure the Where so it doesn't limit anything if "<All>" is selected?

Edit 1:

I tried
WHERE Q4.AgentGroup= iif([Forms]![frmCDErrorReports]![Group]='<All>','*',[Forms]![frmCDErrorReports]![Group])
hoping that the * would allow the where to grab everything, but its just comparing the two, and because no group is = * it isn't giving any results.

Edit 2:

Just tried this also

WHERE iif(Forms!frmCDErrorReports!Group='<All>','','Q4.AgentGroup=Forms!frmCDErrorReports!Group')

Didn't work, always gets all the agent groups.

[BManke]

use "LIKE" instead of =

then the asterisk (*) works fine

WHERE Q4.AgentGroup LIKE iif([Forms]![frmCDErrorReports]![Group]='<All>','*',[Forms]![frmCDErrorReports]![Group])

[anhn]

The "Like" may work but it is very slow if the table is large.

There is a trick. If the filter equal "<All>" then pick any AgentGroup that "equal to itself", ie. Q4.AgentGroup = Q4.AgentGroup.
That means pick all of them (except Null, because we can not compare Null=Null).

WHERE Q4.AgentGroup = IIf([Forms]![frmCDErrorReports]![Group]="<All>", Q4.AgentGroup, [Forms]![frmCDErrorReports]![Group])

For safety, you should also trap Null value as well, otherwise you will get error when the value of combobox [Group] is null (blank).

WHERE Q4.AgentGroup = IIf(([Forms]![frmCDErrorReports]![Group]="<All>") Or ([Forms]![frmCDErrorReports]![Group] Is Null),
Q4.AgentGroup, [Forms]![frmCDErrorReports]![Group])

[si_the_geek]

Another trick is to skip all of the field based comparisons if apt, eg:

WHERE (
          ([Forms]![frmCDErrorReports]![Group]="<All>") 
       OR (Q4.AgentGroup = [Forms]![frmCDErrorReports]![Group]) 
      )

[AND ...]

The first half (the comparison to "<All>") will only run once for the entire query, and if it is "<All>" then the second half (the line starting with "or") will not run at all.

If the value is not "<All>", the standard comparison (the second half) will be made as usual for each row of data.


At worst, this only adds one comparison to the overall query (which for 100+ rows of data is negligible) to using separate 'optimised' queries for each variation.

anhn makes a good point about checking for Null in the control, and this can be added to the first half of this method.

[anhn]

si has shown a good way to write Criteria in this case, that is what I usually do in Access Query design.

However, in Access and VBA, both parts A and B in the expression "A Or B" will be evaluated at any time, ie. B will be evaluated even when A is True.
eg, both of these statements cause Overflow Run-time error:
a = True Or CInt(10 ^ 5)
b = IIf(True, 1, CInt(10 ^ 5))

To save you to repeat a lengthy typing, in Query Design View you can enter:



With that in SQL View you will see for the left:

WHERE ((([Forms]![frmCDErrorReports]![Group]) Is Null 
Or ([Forms]![frmCDErrorReports]![Group])="<All>" 
Or ([Forms]![frmCDErrorReports]![Group])=[Q4].[AgentGroup]));

or for the right:

WHERE ((([Forms]![frmCDErrorReports]![Group]) Is Null)) 
OR ((([Forms]![frmCDErrorReports]![Group])="<All>")) 
OR ((([Forms]![frmCDErrorReports]![Group])=[Q4].[AgentGroup]));

Don't worry about too many extra parentheses, that is the way Access put them.
If writing SQL directly we can remove them:

WHERE ([Forms]![frmCDErrorReports]![Group] Is Null)
OR ([Forms]![frmCDErrorReports]![Group]="<All>") 
OR ([Forms]![frmCDErrorReports]![Group]=[Q4].[AgentGroup]);

That is clear enough, but this is also OK:

WHERE [Forms]![frmCDErrorReports]![Group] Is Null
OR [Forms]![frmCDErrorReports]![Group]="<All>" 
OR [Forms]![frmCDErrorReports]![Group]=[Q4].[AgentGroup];

[rack]

Anhn As always thank you.

Si_The_Geek thank you as well, you have been very helpfull.

I'm going to spend the week changing the database to use what you guys have suggested, instead of me building a filter for the report, as this will both save space, and ensure that the data is filtered before going to the report.