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))

--
Build a little, test a little.


"caro" wrote:

> 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

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" <(E-Mail Removed)> wrote in message
news:50E32200-D97A-4881-8BD8-(E-Mail Removed)...
>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

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" wrote:

> 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" <(E-Mail Removed)> wrote in message
> news:50E32200-D97A-4881-8BD8-(E-Mail Removed)...
> >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
>
>
> .
>

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

caro wrote:
> 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" wrote:
>
>> 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" <(E-Mail Removed)> wrote in message
>> news:50E32200-D97A-4881-8BD8-(E-Mail Removed)...
>>> 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
>>
>> .
>>