IIF statement not working

[grahamsyuk]

hi i am trying to use the following iif statement in a query.

IIf([Forms]![MainForm]![cmb_ExcessStatus]="Resolved",1,IIf([Forms]![MainForm]![cmb_ExcessStatus]="Outstanding",2,IIf([Forms]![MainForm]![cmb_ExcessStatus]="All Records",([MainTable].[Status])=1 Or ([MainTable].[Status])=2,([MainTable].[Status])=1 Or ([MainTable].[Status])=2)))

So, if user selects 'Resolved' then use 1
if the user selects 'Outstanding' then use 2
if the user selects 'All records' then i want both 1 and 2 but this will not work. i also want the default to be 'All records'

Any help would be much appreciated.

 

[GUIDO22]

The problem with your code is that for the ALL RECORDS item in the IIF statement - you are trying to set Status to 1 AND 2 at the same time, this cannot be done as you have it presently - using the OR operator in this context is a conditional operator and not an assignment operator.

"if the user selects 'All records' then i want both 1 and 2 but this will not work"

And anyway - the Status (variable / control) cannot be both values 1 and 2 -it is either 1 OR 2! Why not just set Status to 1 and leave Status value 2 out of the equation?