Expression in query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Sandy Hayman - 20 Nov 2007 11:29 GMT

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

Signature

Sandy Hayman
AA Absolute Access - ACT, Australia

Reply to this Message

Ofer Cohen - 20 Nov 2007 12:33 GMT

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

Signature

Good Luck
BS"D

> I have to put together a report/query that calculates a number of different
> counts.
[quoted text clipped - 6 lines]
>
> Thanks in advance

Reply to this Message

John Spencer - 20 Nov 2007 12:35 GMT

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.

Signature

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

>I have to put together a report/query that calculates a number of different
>counts.
[quoted text clipped - 7 lines]
>
> Thanks in advance

Reply to this Message

Sandy Hayman - 20 Nov 2007 12:44 GMT

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

>I have to put together a report/query that calculates a number of different
>counts.
[quoted text clipped - 7 lines]
>
> Thanks in advance

Reply to this Message