By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,489 Members | 1,945 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,489 IT Pros & Developers. It's quick & easy.

iif in Criteria field

P: 2
Hi I'm trying to use an iif statement in the criteria field of a query.

FRON is just a flag either 0 or 1
MYS02 is a date

if FRON = 0 I want to return entries for MYS02
if FRON = 1 I want to return entries from MYS02

Expand|Select|Wrap|Line Numbers
  1. IIf([Forms]![FRMOPPMAIN]![FRON]=0,[Forms]![FRMOPPMAIN]![MYS02],>[Forms]![FRMOPPMAIN]![MYS02])
I have tried the options by themselves and they work fine I just cant get them to work in the iif.

Attached Images
File Type: jpg Untitled.jpg (31.1 KB, 319 views)
Nov 29 '11 #1
Share this Question
Share on Google+
4 Replies


Rabbit
Expert Mod 10K+
P: 12,315
... Both conditions result in the same thing... Either way you get MYS02.
if FRON = 0 I want to return entries for MYS02
if FRON = 1 I want to return entries from MYS02
These two sentences mean exactly the same thing.
Nov 29 '11 #2

P: 2
Dear Rabbit, look a bit closer.

If FRON=0 I want to return all entries FOR MYS02 (FOR a specific Date)
If FRON=1 I want to return all entries AFTER MYS02(FROM a specific Date)

The false part of the iif starts with > so dates greater than (or later than)

At least that's what I was expecting it to do.
Nov 29 '11 #3

Rabbit
Expert Mod 10K+
P: 12,315
You didn't say after, you said from. From by itself means equals. If you wanted to say after, you should have said after. Or from ... onwards, but from onwards has a slightly different meaning than your intention.

FROM 1/1/2011 means = 1/1/2011
FROM 1/1/2011 ONWARDS means >= 1/1/2011
AFTER 1/1/2011 means > 1/1/2011

Back to your question. You can't use a comparison operator as part of your return value. Instead, use the BETWEEN method. And for the start date, return MYS02 if FRON is 0 and MYS02 + 1 otherwise. And the end date, return MYS02 if FRON is 0 and 12/31/9999 otherwise.
Nov 29 '11 #4

NeoPa
Expert Mod 15k+
P: 31,186
You can't do it like that. You need to think it a little differently.

You haven't told us which field in your recordset you are filtering this way in your criteria (WHERE clause) so I will assume [DateFld] and you can change it to fit your one :

Expand|Select|Wrap|Line Numbers
  1. WHERE ([DateFld] = [Forms]![FRMOPPMAIN]![MYS02])
  2.    OR (([DateFld] > [Forms]![FRMOPPMAIN]![MYS02])
  3.   AND ([Forms]![FRMOPPMAIN]![FRON] = 1))
NB. Though technically your question was correct, it wasn't easy to see what you meant by that (For & From) due to how it was laid out. It may be worth considering that in future. Reading it now, I can understand what you were saying, but I only worked out how to interpret it after you pointed it out.
Nov 30 '11 #5

Post your reply

Sign in to post your reply or Sign up for a free account.