Conditional Count on numeric field

  • Thread starter Thread starter SteveP.
  • Start date Start date
S

SteveP.

I am trying to help members of the purchasing department where I work with a
report that they are currently using. This report contains information
regarding ontime/late deliveries. They use the "PromisedDate" and
"ReceiptDate" to determine whether the product received was early or late or
"on-time" in field "EarlyLate". Currently, this report is sent to an Excel
spreadsheet where they run calculations and make decisions off of this
"EarlyLate" field. What I would like to do is add two fields to this report
- one giving a count of the number of receipts that were more than 10 days
early (the way the report is set up is receipts that are "early" are negative
numbers, and receipts that are "late" are positive) and then a count of the
number of receipts that were late.

I tried using =Sum(Abs([EarlyLate]<-10)), =Sum(Abs([EarlyLate]< "-10")), and
=Count(Abs([EarlyLate]<-10)). The first gave an answer of 26, the second -
96, and the last gave 121 (total number of receipts for the vendor in
question). Manually counting through the records I found 6.

I know it's going to be something that seems fairly simple, but I would
appreciate any help I can get.

Thank you,
Steve
 
Well the first should work if EarlyLate is a number field. The third is
going to count all the values where EarlyLate <-10 is true or false.
Count counts the number of values and true and false are both values.
The second variation is probably doing a string comparison and that is
problematic.

Is EarlyLate a field in the query?

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
 
Why are you using -10 and not just
Early:
=Sum(Abs([EarlyLate]<0))

Late:
=Sum(Abs([EarlyLate]>0))

Smack Dab On Time:
=Sum(Abs([EarlyLate]=0))
 
Company policy is that they don't mind if a vendor gets something to us a
couple days early as long as it isn't too early...hence the -10.

But the rest of that worked great.

Thanks again.
 
Hi John,
Sorry it has taken so long to get back to you on this. EarlyLate is a
calculated numeric field on the report based off of 2 other date fields on
the report. And thank you for the explanations.
Steve

John Spencer said:
Well the first should work if EarlyLate is a number field. The third is
going to count all the values where EarlyLate <-10 is true or false.
Count counts the number of values and true and false are both values.
The second variation is probably doing a string comparison and that is
problematic.

Is EarlyLate a field in the query?

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================


SteveP. said:
I am trying to help members of the purchasing department where I work with a
report that they are currently using. This report contains information
regarding ontime/late deliveries. They use the "PromisedDate" and
"ReceiptDate" to determine whether the product received was early or late or
"on-time" in field "EarlyLate". Currently, this report is sent to an Excel
spreadsheet where they run calculations and make decisions off of this
"EarlyLate" field. What I would like to do is add two fields to this report
- one giving a count of the number of receipts that were more than 10 days
early (the way the report is set up is receipts that are "early" are negative
numbers, and receipts that are "late" are positive) and then a count of the
number of receipts that were late.

I tried using =Sum(Abs([EarlyLate]<-10)), =Sum(Abs([EarlyLate]< "-10")), and
=Count(Abs([EarlyLate]<-10)). The first gave an answer of 26, the second -
96, and the last gave 121 (total number of receipts for the vendor in
question). Manually counting through the records I found 6.

I know it's going to be something that seems fairly simple, but I would
appreciate any help I can get.

Thank you,
Steve
Click to expand...
Click to expand...
 
Back
Top