Expression in query

[Sandy Hayman]

I have to put together a report/query that calculates a number of different
counts.

Is there a way to put together an expression like:
Count(IsNull([SalesInstructionIssued]) AND [SalesInstructionIssued]<Date())

Access accepts this but doesn't give me the correct result. Is there a way
of doing this without using a DCount?

Thanks in advance

 

[Guest]

In the report you can create a text box to count a certain occurence

Something like
=Sum (Abs([SalesInstructionIssued] Is Null AND
[SalesInstructionIssued]<Date()))

The Abs will replace the True (-1) with 1
The Sum will add up all the 1'ns returned when the criteria met

 

[John Spencer]

Count counts the presence of a value. Your expression is going to return
true or false which is always a value.

Try
Abs(Sum([SalesInstructionIssued] Is Null AND
[SalesInstructionIssued]<Date()))

Since False returns 0 and True returns -1 (in Access) you are summing the
number of trues. Abs removes the negative sign.

You could also use
COUNT(IIF([SalesInstructionIssued] Is Null AND
[SalesInstructionIssued]<Date(),"X",Null))

When the expression is true X is returned, otherwise Null is returned.
Count does not count NULLs.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Sandy Hayman]

Wow! Thank you BS"D and John. That was exactly what I needed. Works like a
charm.