query expression too complex?

[supmktg]

The recordsource of a listbox is a query which is filtered by a combo box on the form. There are 4 possible groups to be viewed and the values for each group are Group 1=(Null or " "), Group2="Denied", Group3="Restricted" and Group4=("Approved" or an approval number). The query worked fine until I added the code to get Group4 by eliminating all of the other values. It is causing an 'expression too complex' error. Here is the sql for my query:

SELECT vi_AuthApproval.AuthID, nz([Approval_Number]," ") AS Approval
FROM vi_AuthApproval
WHERE (((nz([Approval_Number]," "))=IIf([Forms]![frmAuthApprovList]![cboApproval]="Approved",(nz([Approval_Number]," "))<>" " And (nz([Approval_Number]," "))<>"Denied" And (nz([Approval_Number]," "))<>"Restricted",[Forms]![frmAuthApprovList]![cboApproval])));

Can someone help me simlify the query or point me in another direction to accomplish this?

Thanks,
Sup

 

[Guus2005]

Create a reference table "ApprovalRef"with two fields: "Group" : number and "Description" : text

ApprovalRef
Group Description
1
2 Denied
3 Restricted
4 Approved

Join it with your table. You will notice that your query is much faster and you can add group ids if you want.

Enjoy!