IIF statement in WHERE clause does not working

[tranchemontaigne]

This query is driving me bonkers.

I just quite cannot figure it out. I have a form with a radio button that stores the value of 0 and -1
This radio button indicates whether capital expenses should be included in the query output.
If the radio button [opt_w_Cap_Exp] = -1 then only records where field t04_Resource_Allocation.[t04_Capital_Expenditure] stores a value of -1 should appear in the output.
If the radio button [opt_w_Cap_Exp] = 0 then all records should appear in the output.

SELECT
t04_Resource_Allocation.t04_Resource_Allocation_ID, t04_Resource_Allocation.[t04_Capital_Expenditure] AS Expr1
FROM t04_Resource_Allocation
WHERE
(
(
(t04_Resource_Allocation.[t04_Capital_Expenditure])=
IIf([Forms]![frmMainMenu]![frmSubform].[Form]![opt_w_Cap_Exp].[value]=-1,(t04_Resource_Allocation.[t04_Capital_Expenditure])<2,0)
)
)
;

The problem is that regardless of whether I write the IIF statement with the 2nd argument as

Like "*"
In(0,-1)
<2
isnull(...) = false
isnumeric(...) = true

I cannot figure out how to conditionally show all records in the output.
Any help would be appreciated.


Environment: MS Access 2000 SP3 on Windows XP
________
Alfredo Ferrari Specifications

 

[DJkarl]

Could you dynamically write the query, so that if the radio button is true you include your where clause, if the radio button is false then just write a query without the where clause so you get everything.

 

[KenHigg]

Or have two queries, Or even two sql strings) one with the data the other without. And on the onOpen event of the report set the recordsource to the appropriate query.

 

[tranchemontaigne]

thanks for the response. This might have been a better solution if there were not so many dependent queries.....

I could write two queries and call them alternately, but I was hoping to do it all in one query.

Do you know why it fails to evaluate properly? All options I have tried are somehow interpretted to be equivalent to a literal -1.
________
Ultimate fighter

 

[KenHigg]

Well, just taking a peek, to start with, something looks a muck in the iif(). You usually have the condition to test, then what you want if that tests true and the want you want if it tests false. IIf(test,if true,if false). Looks like this is what you want if it tests true:

(t04_Resource_Allocation.[t04_Capital_Expenditure])<2

Which kind of don't make sense to me - ? It looks like another evaluation - ?

 

[tranchemontaigne]

Thanks KenHigg, this is helpful.

I guess I misunderstood how IIF works. I thought IIF would return values, and that value could be an expression.

It makes some sense that IIF only returns discrete values, but is a little disappointing.

Your thoughts are appreciated.
________
Subaru srd-1 specifications

 

[KenHigg]

I think I would opt to do what DJkarl suggested. It's a very useful technique.

 

[Brianwarnock]

Try

Where ((t04_Resource_Allocation.[t04_Capital_Expenditure]= -1) and
([Forms]![frmMainMenu]![frmSubform].[Form]![opt_w_Cap_Exp].[value]=-1) or ([Forms]![frmMainMenu]![frmSubform].[Form]![opt_w_Cap_Exp].[value]= 0) = TRUE)

Brian