Count Reports Sent Before A Specific Time

  • Thread starter Thread starter caro
  • Start date Start date
C

caro

I am trying to count the number of reports were sent before 4 pm each day. If
the time difference between 4 PM and when the report is sent is greater than
1, then I want that to count as 1. If the time difference is a negative
number, then I do not want that report to be counted.
My formula so far is:
=Count((DateDiff("m",[4:00 PM],[Preparedness]![Time AM Report Sent])<=1))
But all that is doing is counting all reports. Is there a better/easier way
to do this?
Many thanks!
Caro
 
Parsing your formula I see you are wanting to know how many months between
[4:00 PM] and [Preparedness]![Time AM Report Sent] but based on the names
that is not logical as I would not expect a date in [4:00 PM].
Then it appears you want to text that for a number less than 2 but you did
not include the IIF function.
Below it returns a '1' when true and '0' when false and then sums the totals.

=Sum(IIF(DateDiff("m",[4:00 PM],[Preparedness]![Time AM Report Sent])<=1, 1,
0))
 
We're not there, so you'll need to give a just a bit more to go on ...

When you say "count the number of reports were sent", what do you mean?
Sent where?

If you are referring to reports printed, there's no guarantee that a report
sent to a printer gets there, or is printing in its entirety, or ...

More info, please...

--

Regards

Jeff Boyce
Microsoft Access MVP

Disclaimer: This author may have received products and services mentioned in
this post. Mention and/or description of a product or service herein does
not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.
 
Well, I see I made one huge mistake already, I used "m" when I was supposed
to use "n". I am trying to add up the number of reports that are sent to a
specific person after the cut off time, which is 12:00 pm. The time the
report is sent is entered into a form by an operator, not automated.

Using
=Sum(IIf(DateDiff("n",[Preparedness]![Time AM Report Sent],[12:00PM])>=1,1,0))
all the records are added, not only the ones that sent after 12:00 pm.

-Caro

Jeff Boyce said:
We're not there, so you'll need to give a just a bit more to go on ...

When you say "count the number of reports were sent", what do you mean?
Sent where?

If you are referring to reports printed, there's no guarantee that a report
sent to a printer gets there, or is printing in its entirety, or ...

More info, please...

--

Regards

Jeff Boyce
Microsoft Access MVP

Disclaimer: This author may have received products and services mentioned in
this post. Mention and/or description of a product or service herein does
not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

caro said:
I am trying to count the number of reports were sent before 4 pm each day.
If
the time difference between 4 PM and when the report is sent is greater
than
1, then I want that to count as 1. If the time difference is a negative
number, then I do not want that report to be counted.
My formula so far is:
=Count((DateDiff("m",[4:00 PM],[Preparedness]![Time AM Report Sent])<=1))
But all that is doing is counting all reports. Is there a better/easier
way
to do this?
Many thanks!
Caro
Click to expand...


.
Click to expand...
 
Why not just use the simple expression.
Abs(Sum(Preparedness>#12:00:59#))

If you insist on using the date Diff function then delimit the time correctly.
#12:00:00 PM#

=Sum(IIf(DateDiff("n",[Preparedness]![Time AM Report Sent],#12:00 PM#)>=1,1,0))

Or just use 12:00
John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
Well, I see I made one huge mistake already, I used "m" when I was supposed
to use "n". I am trying to add up the number of reports that are sent to a
specific person after the cut off time, which is 12:00 pm. The time the
report is sent is entered into a form by an operator, not automated.

Using
=Sum(IIf(DateDiff("n",[Preparedness]![Time AM Report Sent],[12:00PM])>=1,1,0))
all the records are added, not only the ones that sent after 12:00 pm.

-Caro

Jeff Boyce said:
We're not there, so you'll need to give a just a bit more to go on ...

When you say "count the number of reports were sent", what do you mean?
Sent where?

If you are referring to reports printed, there's no guarantee that a report
sent to a printer gets there, or is printing in its entirety, or ...

More info, please...

--

Regards

Jeff Boyce
Microsoft Access MVP

Disclaimer: This author may have received products and services mentioned in
this post. Mention and/or description of a product or service herein does
not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

caro said:
I am trying to count the number of reports were sent before 4 pm each day.
If
the time difference between 4 PM and when the report is sent is greater
than
1, then I want that to count as 1. If the time difference is a negative
number, then I do not want that report to be counted.
My formula so far is:
=Count((DateDiff("m",[4:00 PM],[Preparedness]![Time AM Report Sent])<=1))
But all that is doing is counting all reports. Is there a better/easier
way
to do this?
Many thanks!
Caro
Click to expand...

.
Click to expand...
Click to expand...
 
Back
Top