Count IIf With AND and OR Criteria

[Anita Taylor]

I'm trying to count the number of records in a report where the DATE field is
between two user-entered dates and the LEVEL field is either Urgent or High.

I've tried the following, but it appears to be counting all records on the
report, regardless of the actual date in the DATE field.

I cannot use DATE is not null because that date range MUST fall between the
two variables selected by the user.

Any help would be greatly appreciated!

=Count(IIf([DATE] between [BeginningDate] and [EndingDate] and
[LEVEL]="URGENT",1,IIF([DATE] between [BeginningDate] and [EndingDate] and
[LEVEL]="HIGH",1,0)))

 

[NetworkTrade]

IIf([DATE] >= [BeginningDate] and [Date]<=[EndingDate]

 

[John Spencer]

Count counts the presence of a value - 0 is a value. Try using SUM to add the
1's and 0's.

=SUM(...)

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Duane Hookom]

Try:
=Sum(IIf([DATE] between [BeginningDate] and [EndingDate] and
[LEVEL]="URGENT",1,IIF([DATE] between [BeginningDate] and [EndingDate] and
[LEVEL]="HIGH",1,0)))