Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Password Reminder
Register
| FAQ | Members List | Calendar | 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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #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
Reply With Quote
  #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?
Reply With Quote
  #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.
Reply With Quote
  #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
Reply With Quote
  #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
Reply With Quote
  #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
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 10:19 AM.


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