p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   Access (http://p2p.wrox.com/forumdisplay.php?f=18)
-   -   Question with iif (http://p2p.wrox.com/showthread.php?t=35902)

Enoch December 7th, 2005 06:48 PM

Question with iif
 
I'm pretty new to access, and I'm using an iif statement in the criterea section of a query, if Category1 = 1 it means uknown and I'd like to return all ID's,
the following is the if statement I tried

iif([Forms]![frmOutflow]![fsubOutItem]![cboCategory1Select]=1,
>0,
[Forms]![frmOutflow]![fsubOutItem]![cboCategory1Select])

it gives me no errors but the query returns an empty set if Category1 = 1

Now if I put a number like this:

iif([Forms]![frmOutflow]![fsubOutItem]![cboCategory1Select]=1,
4,
[Forms]![frmOutflow]![fsubOutItem]![cboCategory1Select])

It returns all where ID = 4 as expected

And if I try
iif(1,>0,[Forms]![frmOutflow]![fsubOutItem]![cboCategory1Select])
I still get an empty set

Can anyone help me?
Thanks


Enoch

leehambly December 8th, 2005 05:47 AM

the iif function works like this...

iif([Logic to be determined],[case if true],[case if false])

so... what your initial statement says is:

if [Forms]![frmOutflow]![fsubOutItem]![cboCategory1Select]=1 then return the value ">0"

... are you sure this is correct? What does ">0" actually mean and how is it supposed to return that as a single value?

leehambly December 8th, 2005 07:32 AM

missed the fact it is a criteria in a query rather than a value.. shouldnt make much difference though.. simply throw the "iif(" into your query as a value in order to debug it... at least then you can see what you are actually evaluating.

Enoch December 8th, 2005 11:36 AM

Leehambly, thank you for your advice, however I am still having a problem with the iif statement.
I put the original iif statement into the value rather than criterea field except with quotes around the >0 (so it would work). The logic appears to work correctly.
So then I thought, perhaps in the criterea it's trying to literally match >0 to the ID field rather than numbers greater than 0. Maybe I need quotes or something, but when I put quotes around >0 in the criterea section, it gives an error that the expression is too complex.
I know you can nest iif statements but it seems like you can't put any other expressions into the (case if true, case if false) sections of a query. (I'm probably wrong)
Additional help would be appreciated.
Thanks

Enoch

Enoch December 8th, 2005 11:39 AM

Quote:

quote:Originally posted by Enoch


I know you can nest iif statements but it seems like you can't put any other expressions into the (case if true, case if false) sections of a query.
Whoops, by this I meant, I know you can nest iif statements but it seems like you can't put any other expressions into the (case if true, case if false) sections of an iif statement, at least within the criterea section of a query. Sorry that was confusing


Enoch

Enoch December 8th, 2005 04:36 PM

Figured it out!
When you put an iif statement into criterea, in the query menu it appends an "Table.Field =" prior to the iif in the SQL
so the where statement if the iif evaluated true ended up being
Table.Field = >0 which doesn't make much sense

So I used your idea Lee and if you put this in the Value Section
IIf([Forms]![frmOutflow]![fsubOutItem]![cboCategory1Select]=1,
([tblItem].[Category1ID])>0,
([tblItem].[Category1ID])=[Forms]![frmOutflow]![fsubOutItem]![cboCategory1Select])
and then put <> False as Criterea, you get the correct recordset.


Thanks for your help


Enoch


All times are GMT -4. The time now is 02:37 AM.

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.