Dcount syntax help

[scottb]

Hello everyone. Using Access 2007. I am have a form (frmProjectActionItems) with a text box (txtOverdueActionItems) in the form footer within which I'm trying to get a dcount expression to work to tell me how many overdue action items there are. The form's data source that I am pointing the Dcount to is qryActionItemsByProject. I would like a count of all open action items that have a due date greater than now(). So far I have the following which throws an error:
=DCount("[ActionItemID]","[qryActionItemsByProject]","[Status]"='Open' And "[DueDate]"<Now()). Hopefully this is fairly close. Any help would be appreciated. Thank you!

[HansV]

The entire 3rd argument should be a string. Try

=DCount("ActionItemID","qryActionItemsByProject","[Status]='Open' And [DueDate]>Now()")

Your formula has < but your description says "greater than now()", so I changed < to >. You could also try

=Abs(Sum([Status]="Open" And [DueDate]>Now()))

[scottb]

Hans,
That did it. Thank you for your help. Also, I was unfamiliar with ABS. Is there a circumstance were ABS is preferable/more appropriate than Dcount?
Thanks again.
-Scott

[HansV]

The Abs function is a simple mathematical function that converts negative numbers to their positive counterpart:

Abs(36) = 36
Abs(0) = 0
Abs(-17) = 17

In itself, it does not count records, but you can use it in an expression =Abs(Sum(condition)).
condition evaluates to True = -1 or to False = 0.
Sum(condition) adds -1 for each record that satisfies the condition, i.e. it counts the number of records satisfying the condition, but with a minus.
Abs removes the minus.

The expression =Abs(Sum(condition)) looks at the record source of the form/report, so unlike DCount, it doesn't have to open the table or query again. So it will generally be more efficient. But in most situations you won't notice much difference.

[KarenYT]

I like this thread, then I have a little puzzle, sorry, Hans.....

=Abs(Sum([Status]="Open" And [DueDate]>Now()))
The expression =Abs(Sum(condition)) looks at the record source of the form/report, so unlike DCount, it doesn't have to open the table or query again. So it will generally be more efficient. But in most situations you won't notice much difference.

From this thread -
So, "Abs" also calculates the total number of all the "open" records of the query/table not just the record ?

thanks
Karen

[HansV]

Abs itself doesn't count anything, it merely turns negative values into positive ones (and leaves positive values unchanged).

In the expression =Abs(Sum([Status]="Open" And [DueDate]>Now())), the condition [Status]="Open" And [DueDate]>Now() is evaluated for each record. If the condition is satisfied (i.e. if Status is "Open" AND DueDate is later than today), it evaluates to True, which is equivalent to -1 in VBA, and otherwise it evaluates to False, which is equivalent to 0.
So if Status is "Open" AND DueDate is later than today, the record contributes -1 to the sum, otherwise 0. If there are 37 records for which Status is "Open" AND DueDate is later than today, the Sum will be -37. The only thing Abs does, is convert -37 to +37, i.e. the number of records satisfying the condition.

[KarenYT]

Thanks, Hans!