Count IIf With AND and OR Criteria

  • Thread starter Thread starter Anita Taylor
  • Start date Start date
A

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)))
 
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
 
Try:
=Sum(IIf([DATE] between [BeginningDate] and [EndingDate] and
[LEVEL]="URGENT",1,IIF([DATE] between [BeginningDate] and [EndingDate] and
[LEVEL]="HIGH",1,0)))
 
Back
Top