query too complex

[teel73]

I have a query that uses criteria from a form. I want to filter a numeric column named [STAGE] by what the users pick from a combo box on a form. The combo box is named [cboFilStage] and its values are "1,2,3,All stages". If the user selects "All stages" then all records are returned.

In my query, I have this expression:

[[I][B]STAGE[/B][/I]]=[forms]![frmSummaryList]![[I][B]cboFilStage[/B][/I]] Or IIf([forms]![frmSummaryList]![cboFilStage]="All stages",[forms]![frmSummaryList]![cboFilStage] Is Null)

The above expression works fine for all selections except for "All stages". If the user selects "All stages", the expression then returns an error that my expression is too complex.

Can anyone help with this query? Below is the sql query string:

SELECT TrainingView.FirstName, TrainingView.UserId AS txtEmail, TrainingView.Location, TrainingView.Stage
FROM TrainingView
WHERE (((TrainingView.UserId) Is Not Null) AND (([stage]=[forms]![frmSummaryList]![cboFilStage] Or IIf([forms]![frmSummaryList]![cboFilStage]="All stages",[forms]![frmSummaryList]![cboFilStage] Is Null))=True));

 

[pr2-eugin]

I find couple of things that are not right:

1. You are using an IIF which has to have all arguments filled.. So your If is as follows..
IIf(Condition1,Condition2).. But a normal If will be IIf(Condition, TrueStatement, FalseStatement).. So look into that..

2. You said [STAGE] is a Numeric Column, so how can you compare it with "All Stages" which being a String.. So you are on the right track by checking with an If, but you are trying to make it Null which will not give you the desired result; as the Stages are either 1, 2 or 3..
Makes any sense??

 

[vbaInet]

E.g.:

LIKE IIF([Forms]![[COLOR=Blue]FormName[/COLOR]]![[COLOR=Blue]ComboBox[/COLOR]] = "All Stages", "*", [Forms]![[COLOR=Blue]FormName[/COLOR]]![[COLOR=Blue]ComboBox[/COLOR]])

Amend the blue bits.

 

[teel73]

E.g.:

LIKE IIF([Forms]![[COLOR=blue]FormName[/COLOR]]![[COLOR=blue]ComboBox[/COLOR]] = "All Stages", "*", [Forms]![[COLOR=blue]FormName[/COLOR]]![[COLOR=blue]ComboBox[/COLOR]])

Amend the blue bits.

PERFECT!!!!!!!! .. That worked like a charm .. I get it .. Thanks so much.

 

[vbaInet]

You're welcome!