Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old December 7th, 2005, 06:48 PM
Authorized User
 
Join Date: Dec 2005
Location: , , .
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
Default 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
__________________
Enoch
  #2 (permalink)  
Old December 8th, 2005, 05:47 AM
Friend of Wrox
 
Join Date: Sep 2003
Location: Salisbury, Wiltshire, United Kingdom.
Posts: 155
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to leehambly
Default

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?
  #3 (permalink)  
Old December 8th, 2005, 07:32 AM
Friend of Wrox
 
Join Date: Sep 2003
Location: Salisbury, Wiltshire, United Kingdom.
Posts: 155
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to leehambly
Default

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.
  #4 (permalink)  
Old December 8th, 2005, 11:36 AM
Authorized User
 
Join Date: Dec 2005
Location: , , .
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
  #5 (permalink)  
Old December 8th, 2005, 11:39 AM
Authorized User
 
Join Date: Dec 2005
Location: , , .
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
  #6 (permalink)  
Old December 8th, 2005, 04:36 PM
Authorized User
 
Join Date: Dec 2005
Location: , , .
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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


Similar Threads
Thread Thread Starter Forum Replies Last Post
Problems with IIF thork1 Access 1 March 25th, 2008 06:42 AM
help with IIf function rashi Access VBA 3 February 15th, 2008 10:07 AM
IIf umeshtheone Beginning VB 6 3 June 21st, 2007 12:24 AM
IIF Statement Question fastcorvette Access 8 October 31st, 2003 11:01 PM





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