Count Reports Sent Before A Specific Time

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
0
Utf
3/19/2010 3:48:01 PM
access.reports 4434 articles. 0 followers. Follow

4 Replies
814 Views

Similar Articles

[PageSpeed] 4

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
0
Utf
3/19/2010 4:17:01 PM
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" <caro@discussions.microsoft.com> wrote in message 
news:50E32200-D97A-4881-8BD8-B7FCF82941FB@microsoft.com...
>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 


0
Jeff
3/19/2010 4:56:02 PM
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" <caro@discussions.microsoft.com> wrote in message 
> news:50E32200-D97A-4881-8BD8-B7FCF82941FB@microsoft.com...
> >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 
> 
> 
> .
> 
0
Utf
3/19/2010 6:03:01 PM
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" <caro@discussions.microsoft.com> wrote in message 
>> news:50E32200-D97A-4881-8BD8-B7FCF82941FB@microsoft.com...
>>> 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 
>>
>> .
>>
0
John
3/19/2010 6:45:36 PM
Reply:

Similar Artilces:

Report writer #17
I have a client that needs to add a field to the Financial and Payables Edit list... the field they need can't be linked through the tables in Report Writer. How would be the best way for me to pull in the information from Segment Description - only segment 5's description. The only way I can think of accomplishing this is by using Report Writer with VBA. Best regards, -- MG.- Mariano Gomez, MIS, MCP, PMP Maximum Global Business, LLC http://www.maximumglobalbusiness.com "GPNut" wrote: > I have a client that needs to add a field to the Financial and > Payables Edi...

keeping/storing outlook 2007 emails inbox and sent
1. most people get and send many emails 2. after sometime you find that the ealier ones are not so hot now, and you woud like to keep them in a handy place in case a point is to be rechecked, or an email date to be reconfirmed 3. in your opinion what is a simple way to do this, and how should those folders be named? this aplies to both coming in and sent out 4. categories could be first in email on a particular project in the subject say 9854b 5. futher break this as same project, but the same senders for in-mail same for sent to a particular person on a particular projec...

Working with time
I have set up a worksheet calculating employees time worked. As the data inputted is shift type e.g. e, l, n. I have setup a formula to work out each shift by its time length e.g. e = 7.5 hrs etc. As my employees do not all work set convenient hours some work overtime. How can I set a formula for excel to report back if someone works 9hrs 22.5 minutes that they have either worked more or less than their contracted hours in a time vale and not as a part of 100? ...

Extremely Long SFO synchronization times
We are seeing now for a few weeks an average synch time for the SFO client of about 1 hour, originally it was taking around 20-30 minutes. This is only speaking of the Local area network, anyone over the WAN can not even perform this function without deciding to give up at least 2 hours of their time. Our CRM/SQL box would go crazy with one synch and both 2.8 GHz processors would run steadily at 100%, making the CRM site unusable for everyone else. Would it be possible for me to disable publishing, and then enable and create new publications to CRMMetaPub and CRMPub without having to re...

Possible to run a small macro every time I change a value in a cell?
Is this possible in Office XP? Thanks Christian Christian, You can use the Change event to do this. In the code module for the appropriate worksheet, use code like the following. Change the cell address to the cell you need. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$C$3" Then '<<< Change to cell address ' your code here End If End Sub -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Christian Borchgrevink-Lund" <borchgrevink@yahoo.com> wrot...

Hotmail will not receive/display messages sent from GMX or Yahoo
Messages sent from a GMX or Yahoo account using Windows Live Mail will not be received/displayed in Hotmail's inbox. However, if they are sent using Outlook Express, they are successfully received and displayed. Also, a message sent from G-Mail using Windows Live Mail (or Outlook Express) will work fine. Very unusual! When you say "Hotmail inbox" do you mean the one on the web or the one in your Windows Live Mail? Are the missing messages not visible in either location? Hotmail has very stringent (some would say 'draconian') spam filtering. It is not ...

How do I get our Outlook accts to accept emails froma specific dom
I know how to "Accept this domain" in each individual Outlook accout, however, when we add new Outlook accounts we have to go through the process each time. Someone in the office suggested getting a list of all of the emails from that domain and then updating the server. Would this be the best way to do this? Is there a way to get the main Outlook account to accept the domain name and then each time that we add a new account it will automatically accept emails from this domain? What version of Outlook? Do you have the junk mail setting refusing all mail except f...

View one record at a time in a report.
I am new to access. I created a report from a table using the wizard. When I view the report, I only want to see one given record at a time - not all records. How do I do this? Thank you Ron The easiest way is to start from a form, where you bring up the record you want to see. Then put a command button on the form, to open the report to just that one record. Here's the code you need for the command button: Print the record in the form at: http://allenbrowne.com/casu-15.html -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne...

Sent Items disappeared
Hi We are running Outlook 2003, not in cached exchange mode, running on Terminal Server. We have one user who has lost 4 days of sent items from her email. I have checked that the user hasn't deleted the emails and can see them in message tracking on the exchange server. (Exchange 2003 SP2) Any ideas as to why this would happen? Thanks in advance We are having the same problem. Archiving is not set on that folder and her sent emails keep disappearing. Had any luck with the solution yet? "Darren97" wrote: > Hi > > We are running Outlook 2003, not in cached excha...

can't sent email
I use Outlook 2003. I have a paid ISP. I never had a problem till I installed Norton Internet Security; afterwhich I could download but not send. Turning off NIS personal firewall helped for a bit, but not for long. Somehow it got fixed. (???) Recently my hard drive crashed and I put a new one in. Since then I can download email, but cannot send it. It stays in the Outbox. I have tried Help, I have made sure my settings are the same as all the other computers in the network. I know others have had this problem, but I have not seen a solution that works. Thanks, pgregg Turn off AV sca...

Case Billable Time and Total Time in workflow
Hello evereyone, Case Billable Time and Total Time fields don't accessible in workflow! How can I calculate Case duration in workflow or access aforesaid fields? -- Mohsen Ahmadi, msnahm@hotmail.com IR-0912 *** **** ...

Missing items on Stock Count
I have a handful of items that are not showing up on my stock count entry. The items have a zero quantity on hand, item type is sales inventory, and they do not have a lot or serial number. These items have been on the stock count before. Why are they not showing up now? Do you reuse your stock counts or create them each time? Do you have multiple sites defined? It is possible that the item/site record was deleted for one or more items at one or more sites. Use the Site Maintenance screen to assign all items to all sites (it does not over-write any existing data) and try to crea...

Report Taking a Long Time to Print
I have a main report with 2 subreports in it. One of the subreports seems to be holding up the report when it comes to running and printing. When I run the query and/or report for the problem subreport it returns data within 1 minute. When I run the main report, it takes anywhere up to 5 minutes to run and about 12 minutes to print. When this subreport is removed from the main report, running and printing is under 1 minute, so I know this subreport is the problem. I had seen a post similar to this question and I tried the suggestions (removing page breaks, turning force new page ...

can you count the number of letters (inc spaces) in a single sell
I have a spreedsheet which has 3000 rows. Each cell has a different amount of words in it... ie ABC Learning Centre Ferrymead. Is it possible to count the number of characters (letters) including spaces in each cell??? (please note each cell has a different number of letters in it)... or do I have to do this manually? none of the help options seem to help me... Thanks use the formula =len( -- PF Wannab ----------------------------------------------------------------------- PF Wannabe's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2579 View this thread: ...

Can't see categories in Budget Planner/Won't load Monthly Budget report
In the last couple of days, Money stopped showing the categories under the budget details. In the "Review your current budget status" screen, under budget details, it usually shows each income and expense category and how I'm doing so far in the month. Now none of that shows. I have made sure to check View all budget items and show expense and income in the view menu, but this does not help. In addition, Money will no longer load the monthly budget report when I click "Other Tasks, view reports, monthly budget." I have this problem no matter what budget I...

formatting cells to show time format
Hi I wonder if you can help me with the following. I use Office 97 Professional, and Excel 97 to do the following. I have created a spreadsheet that will be used to calculate times & results for Car Rallies. I have sorted out the formatting of the cells so they show hours, minutes, seconds, and down to thousands of a second, using this time format hh:mm:ss.000. I have done the formulas to work out the elapsed times and then total times, by taking the finish time from the start time etc, then adding elapsed times together to get total times. The thing I am trying to sort out is wh...

Counting by month
Hi everyone...my employees are required to do 2 safety inspections each month and if they meet that requirement, at the end of the quarter, they get a small bonus. I have a table where their inspection information goes. It contains empname, area they inspected, date of inspection and some other info not pertinent to this issue. I have several different reports based on queries where I can look at an individual empl and see what areas they inspected and see that they did both inspections for a particular month OR I have one where it shows a list of all empl and gives me just a count of inspec...

Making pretty reports
I am fairly proficient in the use of formulas to produce data that need for a fairly complex mortgage and real estate investmen spreadsheet, but I'm lacking in presentation. I need to be able t print a presentable report for my clients and I've had a difficult tim importing the data from my spreadsheet to Word in a reliable manner ( must not be doing something right, because it seems too tedious an time-consuming). I've tried to format a worksheet in Excel to be abl to present the data in a professional and eye-pleasing manner, bu frankly I'm just bombing-out. DOES ANYONE H...

receiveing the same email three times #2
My outlook express died on me, therefore I had to start using outlook 2000, which was fine bit of a pain to get used to but, anyway since using outlook 2000, I am getting the same email from everyone three times. Why is this? It is soing my head in as my outlook is very full and it is taking me some time to get rid of the ones that are duplicated. PLEASE HELP ANYONE!!!!!!!!!!!!!!!!!!!!! ...

Count 350 SS numbers, exclude duplicates
I need to be able to count information based on approximately 350 social security numbers, exlcuding duplicates. Any ideas on the formula? Thanks so much, Marsha Hi Marsha, One way =SUMPRODUCT((A1:A200<>"")/COUNTIF(A1:A200,A1:A200&"")) -- HTH RP (remove nothere from the email address if mailing direct) "Marsha" <Marsha@discussions.microsoft.com> wrote in message news:E362D9A0-8A06-43E4-81DD-0B2F28C6C18E@microsoft.com... > I need to be able to count information based on approximately 350 social > security numbers, exlcuding duplic...

Cannot see mail in outlook 2000 sent items folder
Hello, I have a weird problem. When I sent a mail, a few days after it desappears from the Sent Items folder. I cannot see any mail I have sent before. But when clicking on the Sent Items icon the folder appears empty and on the bottom of the left side I can see that the folder contains 20 items. Someone else with rights on my mailbox can see those sent items. Any idea? Thanks in advance. ...

Time Difference
Dear All, I have some doubt in Date & Time Format. I have some (time) appearing in a column in this format 7/17/2006 3:20:53 PM.. I need to have a conditional formatting for this in that column. Ie., if the time in the column is less than 20 minutes to the current time (ie.,now()) then it should change to some color. Any help in this regard. Rajkumar -- Rajkumar Eager to learn more about Excel ------------------------------------------------------------------------ Rajkumar's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2159 View this thread: http:/...

Does Outlook download Yahoo's sent folder? If not, How can I downl
Does Outlook download Yahoo's sent folder? If not, How can I download Thanks for your hlp. --Paul Vakerling No. Ask Yahoo!. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. All unsolicited mail sent to my personal account will be deleted without reading. After furious head scratching, PVakerling asked: | Does Outlook download Yahoo's sent folder? If not, How can I download | | Thanks for your hlp. | | | --Paul Vakerling So Outlook will only download the inbox folder? What if I had another folder say,"Sent1", would ...

Exchange 5.5 does not sent mails with attachment
Hello NG we are currently running our Exchange 5.5 server but going to switch to 2003 within this year. Now we have the problem that the server does not sent mails bigger 5MB anymore. Smaller ones is no problem. There is no restiction set in the server settings nor does the firewall or virus checker block them. Any idear?? Best regards Chris Ruetten On Fri, 27 Jan 2006 13:57:59 +0100, "Christoph Ruetten" <c.ruetten@radiometer.de> wrote: >Hello NG > >we are currently running our Exchange 5.5 server but going to switch to 2003 >within this year. > &g...

OWA Calendar Time
In my OWA there is a discrepancy between looking diary entry and when actually double-clicking on it. The appointment times are an hour behind in the details of the appointment.elay. This does not happen on the Outlook client. The time zone, daylight saving settings are correct on the Exchange server, Outlook client, OWA Options settings, client PC as well. Any suggestions will greatly appreciated. Regards Issue resolved. For anyone inetrested in knowing the solution.... Went into the Time Zone settings on the Exchange server and set it to Hobart time zone (we are in Sydney by the way)...