iif in Criteria field

[daovert]

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

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.

[Rabbit]

... 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.

[daovert]

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.

[Rabbit]

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.

[NeoPa]

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 :

WHERE ([DateFld] = [Forms]![FRMOPPMAIN]![MYS02])
   OR (([DateFld] > [Forms]![FRMOPPMAIN]![MYS02])
  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.