running difference between adjacent records in report

I need to calculate the "in-between-time" for two records.  

Example: process 1 starts at 11:00 a.m. and ends at 11:15 a.m.; process 2 
starts at 12:30 p.m. and ends at 12:45 p.m.

I need to calculate the difference between the end of process 1 and the 
beginning of process 2 in an Access 2003 report.

This is sort of the opposite of a running sum, but I cannot figure out if I 
can manipulate the running sum function to find the answer.
0
Utf
1/2/2008 10:10:07 PM
access.reports 4434 articles. 0 followers. Follow

1 Replies
1670 Views

Similar Articles

[PageSpeed] 4

You can do this by declaring a variable to hold the ending date/time from 
the previous record, so you can compare it to the starting time from the 
current record. You use the report's events to assign this.

Steps:

1. In the General Declarations section of the report's module (at the time, 
with the Option statements), add the line:
    Dim mvarEnd As Variant

2. In the Open event of the report, initialize your variable to Null:
    Private Sub Report_Open(Cancel As Integer)
        mvarEnd = Null
    End Sub

3. In the Print event procedure of the Detail section, assign the value of 
the end date/time field to your variable (substituting your field name for 
TheEnd):
    Private Sub Detail_Print(...
        mvarEnd = Me.[TheEnd]
    End Sub

4. In the Format event procedure of the Detail section, assign the number 
minutes to the unbound text box you set up to display this. (TheBreak is the 
unbound text box; TheStart if the name of your date/time field.)
    Private Sub Detail_Format(...
        If IsNull(mvarEnd) Or IsNull(Me.[TheStart]) Then
            Me.[TheBreak] = Null
        Else
            Me.[TheBreak] = DateDiff("n", mvarEnd, Me.[TheStart])
        End If
    End Sub

-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"TheWireMonkey" <TheWireMonkey@discussions.microsoft.com> wrote in message
news:D6CC6D10-890C-4804-9C49-4FA79D3ED230@microsoft.com...
>I need to calculate the "in-between-time" for two records.
>
> Example: process 1 starts at 11:00 a.m. and ends at 11:15 a.m.; process 2
> starts at 12:30 p.m. and ends at 12:45 p.m.
>
> I need to calculate the difference between the end of process 1 and the
> beginning of process 2 in an Access 2003 report.
>
> This is sort of the opposite of a running sum, but I cannot figure out if 
> I
> can manipulate the running sum function to find the answer. 

0
Allen
1/3/2008 1:05:00 AM
Reply:

Similar Artilces:

CRM 3.0
Hi All, I am writing a custom report for CRM 3.0 to basically copy the My Activities view but display the regarding and To contacts and associated phone numbers. The report is basically done except for a few small issues. I would like to set up the Dynamic Drill-Through so when the person clicks on the Activity Subject it will open the associated Activity record. Unfortunately I have been unable to find the information needed to use in the following code to set the Object Type Code (OTC) to the correct activity type: = Parameters!CRM_URL.Value & "?ID={"&Fields!Activityid...

recording changes
I have a field called "TimeStamp" What I would like is that if someone modifiys or make any change in the form that the "TimeStamp" will record the time and date of change. How can this be achieved? sandrao In an event procedure in the form's AfterUpdate event, you could do something like: Me!txtYourTimeStampField = Now() Note that if you use the BeforeUpdate event, every time your code changes the value of this field, the form tries to update, the BeforeUpdate (re-)triggers, and you "loop up" (at least, it seems to me I've seen this...)...

Exchange Reporting #2
Hello there. We are using Exchange Server 2003 in our company. My management would like me to pull a report stating how many inbound messages are received each day by each recipient in exchange. Is this possible? I have not seen any reporting functions in the Exchange Manager which would allow me to do this. Sean MIS Systems Manager It is not possible natively. You would need to look into a 3rd party monitoring/reporting solution in order to achieve this. Something like Promodag Reports, or Quest MessageStats ought to be able to provide this information. -- Ben Winzenz Exchange M...

Frx from Reports Menu in GP
From the Reports menu Financial > FRx. The Report Launcher opens instead of the Report Designer. How do you change this? Locate the FRXDYN.INI in the application folder you start DynamicsGP. Delete it. Next time you use the Reports--> Financial --> FRX, you should be prompted again for the location. Select the frx32.exe, rather than the frx launch32.exe ------ Robert "6538" <lbeach@remingtonconsulting.com> wrote in message news:OkyfOBGvHHA.4332@TK2MSFTNGP06.phx.gbl... > From the Reports menu Financial > FRx. The Report Launcher opens instead > o...

Changing Costs in Detailed Sales Report
I just opened a new location and I have specified my costs in HQ Mgr under Supplier Tab in Item properties. And I also checked the Supplier tab in Item properties in the Store Ops Mgr, my cost is there. But when I ran a Detailed Sales report for yesterday, some of my items' costs are missing and listed as $0.00. So I open HQ Mgr and issued Worksheet 302 for the new location. I did a couple of test transactions today and ran a Detailed Sales report for today, and it shows the costs of the the test trasactions. But detailed sales report for yesterday still shows $0.00 costs. How do...

SQL Server Reporting Services Reports in Access
Using Access 2007 and SQL Server 2008. Is there a way to display SQL Server Reporting Services reports in Access 2007? If so, links to documentation/examples would be appreciated. -- AG Email: npATadhdataDOTcom Hi AG, No,there is no compatible interface for converting SSRS report to Access 2007 now.You may consider first export your SSRS report to an Excel file and then embed an OLE object "Microsoft Office Excel 97-2003 Worksheet". If you only need the data, you can directly import data from Excel to your Access database. Best regards, Charles Wa...

Cannot view itme in Public Folder between 2 different exchange 2003
I have 2 Exchange 2003 on WIN2k server. Exchange 2003 (A) has 300 mailboxes and store the public folder. Exchange 2003 (B) has 100 mailboxes. User at Exchange 2003 (B) found that they can view the public folder but no items in it. User at Exchange 2003 (A) do not have this problem. Is the public folder must replicate to other server? Is any suggestion to view the item if no replication on public folder ?? Thanks Dennis JoJo These 2 servers in the same routing group. Exchange 2003 (A) is the Master Exchange 2003 (B) is the Member "omer maydan" <omermaydan@yahoo.com> wr...

Reports and making it look prettier: Last Name, First Name Rank
I'm trying to pretty up my report by eliminating the forced space created by having one field of the report for 'LastName', one for 'FirstName', and one for 'Rank.' The Rank isn't too huge of an issue, and if three items in one field gets to be too much, I have no problem leaving that as a side item of sorts. But, I want my report to look a bit better by putting the names together! I want the report to go to my table, pull the LastName from that column, and pair it with the matching FirstName in the column to the right. (Since it's just...

Possible to Look Up Records by Initials?
I have a user who's telling me that prior to this weekend in CRM, she could lookup user records by initials rather than typing in part of the name. I don't see where that setting would have been marked in CRM. Has anyone else heard of this? If so, how did you implement this type of search customization/configuration? Steps to recreate problem: Account screen Account Owner field's magnifying glass Assign Account Assign to another user fields magnifiying glass Look Up Records' Look For field = initials of user you're attempting to Find. I doubt you can use the init...

How to record percentage increases in materials costing sheet.
I am trying to do a materials costing sheet where I would like to be able to periodically increast the base cost of the product by different percentages. E.g. Cost of Apples $10.00 but might be increased by 5% then 2.5% and therefore the base cost would keep changing. I know how do first % increase but not how to then use that figure as the base figure for future adjustments. Thanks for amy help. I am very new to this. You could put a % in one cell. Select the cell and give it a name by Insert-->Name-->Define. Call it "increase". Then, in the cell where you put the f...

HOWTO create a Mail Merge Template in MSCRM with multiple child records
Hi, I have created successfully a basic Mail Merge template in MS Word in Dynamics CRM 4.0 for Order Entity. My challenge now is how to design a template that retrieves the order details (product and prices) related to the order record? This sounds like a parent-child template. Any idea? On Jun 2, 1:26=A0pm, "Benjie Fallar III" <bfall...@hotmail.com> wrote: > Hi, > I have created successfully a basic Mail Merge template in MS Word in > Dynamics CRM 4.0 for Order Entity. > My challenge now is how to design a template that retrieves the order > details (prod...

Adding % of total sales to a sales report
How would I add a field that shows what percentage is the value of sales for an item compared to the report total? -- Geoff Geoff, Which report and what do you want to see? -- * Get Secure! - www.microsoft.com/security You must be using Outlook Express or some other type of newsgroup reader to see and download the file attachment. If you are not using a reader, follow the link below to setup Outlook Express. Click on "Open with newsreader" under the MS Retail Management System on the right. http://tinyurl.com/75bgz ********** "Geoff" <Geoff@discussions.micros...

Recording ownership in an LLC partnership
I'm a partner in an LLC company and would like to track my ownership interest in Money 2004. It's not "real" obvious how I might go about this, particularly ... What account type should be used? How do I record my portion of the profits earned, for which I'm am personally taxed? How do I record the gain in ownership value these profits provided when I haven't necessarily received them in cash? How do I record a portion of these profits as received in cash when they aren't dividends but distributions? I'd prefer to use an investment oriented approach rather ...

How can I autofill a series to reference non adjacent cells?
I need to create a list of values by referencing a series of non adjacent cells (they are separated by a set number of rows each but in the same column). For example the cells should read something like =b2 =b4 =b6 =b8. Of course I could type these in individually but threre are something like 100 values. Is there a way to auto fill this? Look at the indirect function for your example =indirect("B"&(row()*2)) copied down a column will give you a list for non adjacencies. "Microcell" wrote: > I need to create a list of values by referencing a series of...

Multiple indexes on same fields in different order
I sometimes need to find the largest or smallest value in a field. If I put two indexes on the same field, one in ascending order and one in descending order, is Jet smart enough to use the proper index if a give it a grouping query with the condition Max or Min, or am I just wasting resources? Pete -- This e-mail address is fake, to keep spammers and their address harvesters out of my hair. If you want to get in touch personally, I am 'pdanes' and I use yahoo mail. But please use the newsgroup when possible, so that all may benefit from the exchange of ideas. ...

Windows and Mac have two distinctly different units for column width.
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel I am trying to format columns for some data I am entering in a spreadsheet and when I enter &quot;15.00&quot;, which is the required width for these columns given by my professor, I end up with a column 15 inches wide. What I would prefer is for the options to be like the default options in Windows version of Excel. In Windows version of Excel, when you hover over the lines between the columns it gives you two numbers (e.g. 8.43 (64 pixels)). These are the default numbers for column width in Windows Excel....

Can you record a macro in Publisher like you can in Excel?
In Excel I use the feature to Record a Macro. Excel records the keystrokes and converts to VB. Does Publisher have that option? In my version, Publisher 2003, I can Create a Macro, and it takes me into VB. However, I don't know how to code VB. Darlene wrote: > In Excel I use the feature to Record a Macro. Excel records the keystrokes > and converts to VB. Does Publisher have that option? In my version, > Publisher 2003, I can Create a Macro, and it takes me into VB. However, I > don't know how to code VB. Publisher does not have a Macro Recorder, sorry. --...

modify description field in reports
When I print my PO's I can make the part # field in the view report screen wider but when i print it, it defaults back to the regular size. The problem is it cuts off my part numbers on some of my long numbers. Is there a way to overcome this? you must modify the po.xml file directly. "www.choppersapparel.com" <www.choppersapparel.com@discussions.microsoft.com> wrote in message news:8B4E31D5-10A7-4A99-913D-F8F6E3BD7043@microsoft.com... > When I print my PO's I can make the part # field in the view report screen > wider but when i print it, it defaults ...

HELP
I just wrote a check to the IRS, and in the Expense Catagory I indicated "Taxes : Federal Income Tax". Yet in the Income/Expense report, this payment is not included in my total federal taxes paid for this month. I should note that the check was written from an Investment Account. If I write the check from my checking account, the amount correctly shows up in the Income/Expense report. Any ideas as to why it works differently, depending on which account I pay the tax from? In microsoft.public.money, Tom wrote: >I just wrote a check to the IRS, and in the Expense Cat...

Choose Profile Message When Sending Report to Mail
We are having a problem with Mail Configuration. We are using GP published through Citrix. We log into the desktop as a particular user, and configure the default Mail Profile using Mail in the Control Panel, which is Outlook. The problem is that whenever we Print a report to Screen, then go Send Mail as text, it pops up with the Choose Mail dialog. After clicking on Options (on the Dialog) you can check "Default Profile" and it sends the email. Yet when we close GP and re-open it, the Choose Profile dialog appears again. Any ideas why this is happening? Regards Konrad Konr...

Add range options to Rec Dist report
Add additonal range options to the Receivings Distribution report - only by Audit Trail is allowed ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Community/NewsGroups/dgbrowser/en-us/default.mspx?mid=b0268d6b-4b6a-4805-9b52-e...

Two different cells
I have a file with more the 1000 names and addresses. My proble is that the firat and last names are in 2 differents cells. I i would like to put it in teh same cell. Is there anyway I csn do that? Please help too many names to re-type Carlos Assuming that Column A contains the first name and Column B the last name... =A1&" "&B1 OR =B1&", "&A1 ....depending on your preference. Hope this helps! In article <520FA0CA-1940-42E5-B84D-22BA15D59638@microsoft.com>, "Hombreck" <Hombreck@discussions.microsoft.com> wrote: > I have a...

Can decimal point symbol (.) be removed on a Report?
Is there a way in Report Writer to modify a currency field so that it does not show the decimal point symbol (.) - a period, but still shows the 2 decimal places? For example, the field I am trying to modify is Check Amount. Say I have a check for $271.29, I want to format the field so that the outcome is 27129, in other words I don't want the dot/period in between the dollars and the cents. Is this possible to do? If so how can I modify field to display this way? P.S. I don't need the dollar sign either. easiest way would be to use the value in a calculated field with result ...

Limiting data in a report
I have a field in my database that contains only single letters which refer to the specific type of data contained in the other fields. ("A" refers to one specific type while "B" refers to another, etc.) I am attempting to set up an input box which will allow the user to produce a report consisting of data related to "A", "B" or whichever type is desired. Private Sub Report_Open(Cancel As Integer) x = InputBox("C for Airmail, R for Revenues, O for Official Stamps. Leave blank for all") Reports![Wish List]![Prefix]![Criteria] = ...

Table headers in a list box (Custom Report)
I have a big table with many fields (more than fifty). They are not in one table but related. Now user's come with a requirement of a separate type of report everyday which has different fields. because of which i have to design a report for them with the required query. I was therefore wondering can i make a form which has two list boxes the one on left side will have all the fields of the table (only headers needed, not data). Users can select multiple no. of fields from that list box which will appear in another list box. Users should be able to then select the order of fi...