Multiple Criteria in Control Source Statement

[FrankMars]

I am trying to create a report which only counts the number of records based on a few different criteria. In the control source of a field on the report, I have entered;

=DCount([ID],'[qMain]','[Status] ="Active"') - this returns the correct number of records.

=DCount([ID],'[qMain]','[Price]<1000') - this returns the correct number of records.

When I combine both of these criteria in one statement as follows;

=DCount([ID],'[qMain]','[Status] ="Active"' And '[Price]<1000') - this INCORRECTLY returns ALL records.

Is it possible to write a statement in the control source of a field with multiple criteria? What am I doing wrong?

Thanks in advance.

 

[dhookom]

I don't care for using domain aggregate functions in queries or reports. However, you can triy:

=DCount('[ID]','[qMain]','[Status] ="Active" And [Price]<1000')

or in US

=DCount("[ID]","[qMain]","[Status] ='Active' And [Price]<1000")

If the record source of the report is qMain then simply use:

 =Sum(Abs("[Status] ='Active' And [Price]<1000"))

Duane
Hook'D on Access
MS Access MVP

 

[FrankMars]

Duane - I successfully used your second line of code.

Thanks again.