IIF(OR possible in Access?

[Madmonty]

PLEASE HELP!!

I am trying to build an expression in an Access report that looks at a Main Query (record source of all learners) and asks it specific questions. I am basically trying to write an IIf statement similar to below with an OR clause. I know it is wrong because the OR clause messes it up when I build the expression, but what I am trying to ask Access is

[code]=Sum(IIf([TR_Start]<#01/08/2011# And [TR_End] Is Null OR [TR_End] >#31/07/2011# And [progtype] Like "99" And [agecode] like "1",1,0))[\CODE]

[TR_Start]

is learners' start date

[TR_End]

is the learners' end date

[agecode]

is for singling out age ranges in categories from 1-4. 1 is 16-18 yr olds

[progtype]

is type of programme they are studying. 99 is NVQ Training

I basically want to take a snapshot view of specific learners at the 1st day of each month. So I want learners with agecode of 1 who are on a progtype of 99 who also have a null end date OR a greater end date than the last day of the preceding month AND a training start date less than the first day of the month in question. I can achieve this

is Null or <

in the criteria of a query but not in an expression in a text box on a report.

Can anyone please offer any straightforward solution?

Any help greatly appreciated. :-)

[NeoPa]

Parentheses "()" should do the trick for you :

=Sum(IIf([TR_Start]<#01/08/2011# And ([TR_End] Is Null Or [TR_End] >#31/07/2011#) And [progtype]='99' And [agecode]='1',1,0))

Alternatively, in this particular situation, you could use the Nz() function instead :

=Sum(IIf([TR_Start]<#01/08/2011# And Nz([TR_End],#31/12/9999#)>#31/07/2011#) And [progtype]='99' And [agecode]='1',1,0))

[Madmonty]

Thank NeoPa! That worked a treat!! You're a star!!

[NeoPa]

Always pleased to help :-)