IIF Criteria Help Needed

[GendoPose]

Hi All,

In my query, I have the following criteria;

IIf([MAIL DATE]<Date() And [COMPLETED]=False,[MAIL DATE]<Date(),[MAIL DATE] Between Date() And (Date()+1))

However, this brings up no results.

I just want it to look at all the records and find the ones that aren't completed before today, bring them up and then also show all the records that are for today and tomorrow.

Any ideas at all?

Thanks!

 

[pr2-eugin]

You are not using that in the Criteria of the Query are you?

 

[GendoPose]

You are not using that in the Criteria of the Query are you?

Yes, but using it as an expression poses the same problem.

 

[pr2-eugin]

For a pre compiled query you cannot do this. In VBA yes, but not in the Query builder.

 

[GendoPose]

For a pre compiled query you cannot do this. In VBA yes, but not in the Query builder.

Ah, ok. Could I try doing this with a Union query?

 

[pr2-eugin]

Ummm.. Use VBA? Show the complete Query if you want help converting this to VBA.

 

[GendoPose]

Ummm.. Use VBA? Show the complete Query if you want help converting this to VBA.

SELECT [MASTER PLANNER].*, [MASTER PLANNER].[MAIL DATE]
FROM [MASTER PLANNER]
WHERE ((([MASTER PLANNER].[MAIL DATE])=IIf([MAIL DATE]<Date() And [COMPLETED]=False,[MAIL DATE]<Date(),[MAIL DATE] Between Date() And (Date()+1))));

That's the complete SQL with the criteria in.

 

[vbaInet]

Looks like Paul's tied up, so here.
In a field of the query:

[COLOR="Blue"]Expr1[/COLOR]: IIf([MAIL DATE]<Date() And [COMPLETED]=False OR [MAIL DATE] Between Date() And (Date()+1), Null, 0)

Under the criteria of this field:

Is Null

Edit: I suppose he wasn't. I was just slow to refresh the page

 

[GendoPose]

Looks like Paul's tied up, so here.
In a field of the query:

[COLOR=blue]Expr1[/COLOR]: IIf([MAIL DATE]<Date() And [COMPLETED]=False OR [MAIL DATE] Between Date() And (Date()+1), Null, 0)

Under the criteria of this field:

Is Null

Edit: I suppose he wasn't. I was just slow to refresh the page

Hah no worries, this has got it perfectly! Thank you both for your help!