Date Difference Calculation Between two Records

If somebody can help me , regarding calculating difference of days between to
records.

Ex:

Record No:     Date:                  Receipts       Payments
123               01/04/2007           10000
456               10/04/2007                                  5000
789               20/05/2007            500

What i need is calculate date difference in two records

Record No:       Diff
456                  9 Days
789                  40 Days

I need this days for interest calculation
if somebody could please suggest how to calculate this date difference in
query
i will be very much grateful for this solution.

Vinay.

0
Vinay
5/28/2007 3:44:16 PM
access.reports 4434 articles. 0 followers. Follow

2 Replies
2350 Views

Similar Articles

[PageSpeed] 20

You can use as subquery in a DateDiff Calculation

SELECT [Record No],
DateDiff("d",
   NZ((SELECT Max([Date])
    FROM YourTable as Tmp
    WHERE Tmp.[Date] < YourTable.[Date]),YourTable.Date),YourTable.Date) 
as Elapsed
FROM YourTable

That should return three records
123 : 0
456 : 9
789 : 40


'====================================================
  John Spencer
  Access MVP 2002-2005, 2007
  Center for Health Program Development and Management
  University of Maryland Baltimore County
'====================================================


Vinay wrote:
> If somebody can help me , regarding calculating difference of days between to
> records.
> 
> Ex:
> 
> Record No:     Date:                  Receipts       Payments
> 123               01/04/2007           10000
> 456               10/04/2007                                  5000
> 789               20/05/2007            500
> 
> What i need is calculate date difference in two records
> 
> Record No:       Diff
> 456                  9 Days
> 789                  40 Days
> 
> I need this days for interest calculation
> if somebody could please suggest how to calculate this date difference in
> query
> i will be very much grateful for this solution.
> 
> Vinay.
> 
0
John
5/28/2007 5:41:23 PM
Vinay wrote:

>If somebody can help me , regarding calculating difference of days between to
>records.
>
>Ex:
>
>Record No:     Date:                  Receipts       Payments
>123               01/04/2007           10000
>456               10/04/2007                                  5000
>789               20/05/2007            500
>
>What i need is calculate date difference in two records
>
>Record No:       Diff
>456                  9 Days
>789                  40 Days
>
>I need this days for interest calculation

You can use this kind of expression in a textbox:
=DateDiff("d", DMax("[Date]", "yourtable", "[Date] < " &
Format([Date], "\#yyyy\-m\-d\#"), [Date])

If you will need to sum those values, you may be better
using that as a calculated field in the report's record
source query.

-- 
Marsh
MVP [MS Access]
0
Marshall
5/28/2007 6:01:35 PM
Reply:

Similar Artilces:

Two spreadsheets linked
Hi All I have two spreadsheets which are linked together. Spreadsheet a is linked to b so that in spreadsheet b it pulls in the cells B6:B17 from spreadsheet a. Unfortunately I need it to pull in b5:b16 so what I'm trying to do is to delete a row in spreadsheet a. But when I do that the cell reference in spreadsheet b automatically changes. Is there anyway to stop it automatically changing? Reason I;m not just changing the reference is that there are actually about 26 spreadsheets that are like spreadsheet a which are linked to b. And this would be the quickest way of changing the...

Merging Two Publisher 2003 doc togther
I have a magazine that i create in Publisher and i then get some more content in Publisher that is done by another colleague.. I always have to print it off speratley but was wondering if anyone knew if i could merge the two togther and print it out as one Using MS Publisher 2003 SP2 -- Matt Owner MSE IT www.mse-it.co.uk If it were me I'd make them both pdf files and then combine them with a pdf tools program. PDF-Tools http://www.docu-track.com/downloads/users/ It can be used with any pdf files. Also, their PDF-XChange is a great pdf converting program. -- Don Vancouver, USA ...

Unable to report selected records in Contact and Account
Hello, I have a very strange problem. I have to make a report from the entity Contact (and Account). I have maked a report in Visual Studio 2005. When I run the report on selected records then I get a blank report-screen. When I do exact the same on an own/new entity then it works fine. Can anyone help me? Thanks in advance, Dirk ...

Sync "Sent Items" on two computers
I have a Notebook and a desktop system, both use identical outlook 2002 configs for sending and recieving email. I have my email set up to "Leave a Duplicate Copy" on the server so I can get the same incoming emails on both my systems. My problem is I want to somehow sync my "Sent Items" so that each computer is Identical in that regard as well. Please Please help me get a handle on this. Many thanks, Shane Centerville, UT rsownbey@msn.com 801-292-9069 801-554-4142 Hello Shane, Have a look at www.slipstick.com/outlook/sync.html or at my signature for a powerful...

a problem when two users add data in the same form simultaneously
I have DB(Access 97) where multi- user work in a network.Today, I encountered this problem.Two users were in it and do edition and adding a new data on the sameform at the same time.User 1 was adding data on the form (just say form A) and user 2 wasadding data as well on the same form(A) at the same time.The problem is that user 2 could see data entered by user 1 on thatform(A) at that moment.As I know, access handles problems like this by itself. Ex) provide awarning...Or something I have to check VB code?My question?Are there Adding conflicts in ACCESS 97 (windows XP) between multi-user in a...

Sum of calculated field
I have 3 columns on a report - UnitCost, Quantity, and Credit (calculated on the report as "=[UnitCost]*[Quantity]"). What I am trying to do is add a text box in the report footer that gives me a total of the "Credit" field. Could some one please help me with this. Thanks in advance for any help. SteveP SteveP wrote: >I have 3 columns on a report - UnitCost, Quantity, and Credit (calculated on >the report as "=[UnitCost]*[Quantity]"). What I am trying to do is add a >text box in the report footer that gives me a total of the "Credit"...

Auto Calculation
We use Excel 2003 a lot on our server. Recently we have started to find speadsheets with auto calcualtion switched to manual. My questions are how can this option be switched on i.e. does it come from a template. Is there an easy way to find all occurrances of spreadsheets with this indicator swtched to manual. Finally, anyway of overriding the manual indicator so that automatic calculation so it is always switched on... many thanks for your assistance with this... Peter -- Mahprr ------------------------------------------------------------------------ Mahprr's Profile: http://www.ex...

Adding "Create date" to OL2003 contact form
I would like to add a couple of new fields to my OL2003 contact form: one would automatically note when the particular contact was created, and one would note the date when it was last edited. I am no VB Script expert...is there a simple way to edit the standard form to add these two fields, or am I whistling in the wind? Also, i recently downloaded the Personal Folders Backup add-in from Microsoft and installed it successful. However, I cannot figure out how to use it. The instructions tell me to go to File --> Backup but there is no "backup" option. I ran a search for the progr...

2 different checkbook id's, but truly only 1 checkbook
2 different users are posting into "what they thought" was the same checkbook id, but actually is 2 different checkbooks. So I want to transfer all of the data from one of the checkbooks to the other checkbook, then inactivate that one. I thought there might be something in SQL to do this, but MS Support says no. There is lots of data in both, do you have any suggestions on how to get rid of one of the checkbooks and merge to the other checkbook? Thanks. -- cscheible There are quite a few complications to this request. My first question, which can change some of my other ...

Receipt template
Hi! I'm making custom receipt template. I use different headers ("OverlayFirstPage" and "OverlayOnlyPage" differs from "OverlayMiddlePage" and "OverlayLastPage"). Problem is that these headers are of quite different size and there is a lot of space between header and document section on middle and last page. Can I define different <TOP> parameters in <DOCUMENT> depending on what page it is (or what overlay uses)? Thanks, Koit I got it myself, using page number: <IF> <CONDITION>\p=1 | \p=\t</CONDITION> <T...

SFO Install on a different domain
Our sales people want to have Sales for Outlook installed onto their laptops. Unfortunately, these laptops aren't part of the domain CRM runs on and never will be, so the install fails when it asks for the URL of the CRM server. Has anyone worked out a way around the same domain requirement for installing SFO? They can access the crm web page if neessary but we would prefer to use the Outlook client. ...

macro to calculate or make/update formula
i have the following in cell D2: "=IF(C2<C4,D4,IF(C2<C5,D5,IF(C2<C6,D6,IF(C2<C7,D7,IF(C2<C8,D8,D9)))))" basically, in C2 is a value; column B from row 4 down has the lower bracket and column C from row 4 down has the upper bracket; Column D will have another value that will calculate back into D2. ex: MIN MAX RESULT 0 100 28 101 200 25 201 300 22 301 18 and so forth..., if C2 has a value of 223, D2 would show 22. the problem is sometimes the min and max values will change and the amount of brackets will als...

Warning Beeping and Date Control
I added a Microsoft Monthview Control 6.0 SP4 to a form, and on the On Enter event of a few date controls on a subform, I set the controlsource property to the value of that control. The problem is that every couple of minutes the computer beeps a warning sound when this control exists. When I take it off, there is no problem. What could be the cause of this beeping. I thought that perhaps it is trying to say that there is no control source to the date control (when I haven't set it). It seems that this is not the problem. Can anyone help me? Moshe ...

Last Contact Date 07-27-07
Hi Everyone, I hope someone can assist me. I would like to create a field on the Contact Entity called “Last Contact Date” (this was a cool field that my previous CRM application had). I have no problem creating the field, but I am looking for the best way to populate the field. Any suggestions would be greatly appreciated. Regards, A.Akin Hi, Where do you get the last contact date data from? populated with Last Modified Date? The easiest way is to do an SQL update. I have done that many times to a datetime field and it's not going to break anything. Just remember this is at you...

Calculating the difference between two fields
Hello, I have a subform in a table which has the following controls: Type of Day....Begin Date.....End Date....Total Days There may be several rows of information in these controls. The form is used to calculate the number of vacation days a person has taken. I have written some code that uses that DateDiff function to give me the difference between Begin Date and End Date. The result is supposed to go into the TOTAL Days control. My problem is that it appears that the calculation is performed only once. So for instance, if John Doe has as his first row Begin Date 3/13/2007 and End ...

calculate in form
work with Xp and Access2003 I want to do following In an Input form I want to check if some data aore correct. based on invoices I introduce the BasicAmount, the txes and the total of the invoice i.e. the sum of Basic + Taxes say i.e. 100 + 21 = 121 I intorduce the 100, the 21 and the 121. As matter of contol I compare the calculated sum of 100+21 and compare with the inputed 121. If there is a difference I show an error message. My problem is now that for SOME records the message appears although the 3 inputs are correct. As futher detail, all nummers are "single". I...

how do I remove the date the document was printed
How do I remove the date from the document that indicates when it was printed? There's no option within the program to edit out just that one Property. If you want to remove all personal info from the file [assuming you're using Office 2007] go to Office Button> Prepare> Inspect Document. HTH |:>) Bob Jones [MVP] Office:Mac On 12/31/09 9:31 AM, in article A8749819-D111-44A1-BE64-838E706E7260@microsoft.com, "Bob" <Bob@discussions.microsoft.com> wrote: > How do I remove the date from the document that indicates when it was printed? I...

Do not calculate or update formula's during macro
Is there so code to keep formulas in a spreadsheet from updating? I have a macro that changes sheet names and location of some data but I do not want the formulas referring to those sheets to update. In other words, my formula may read ='Section 2'!A2-'Section 3'!A2 Part of my macro is to rename my sheets so 'Section 3" becomes "Section 2" and "Section 2" becomes "Section 1". After my macro, the formula ends up reading ='Section 1'!A2-'Section 2'!A2 I want to avoid that happening. I don't want my for...

Counting dates in a pivot table
I am trying to make a pivot table together with a chart in which I count the number of events in a month. Basically I have a long list with dates of which I am creating a pivot table in which I am counting the number of dates per month, so far so good. However when there are no entries for a certain month the pivot table is skipping this month. For example when there are no May-dates it would look like this: Mar 25 Apr 96 Jun 64 etc. Is it possible to make a pivot table which would say: Mar 25 Apr 96 May 0 Jun 64 When making a graph this would prevent mis-interpretation when cert...

How do I stop dates from being entered everytime I enter a number
Everytime I enter data in a box (ex: ages 5-9) I then press enter to go to the next cell and it automatically puts in may 9 . I have tried putting in general in formatting cells and it still does it. can someone help before I shoot this pc out the window? I am making a population pyramid with males and females and their ages. Thanks, Kicking and Screaming You can precede your entry with a single quote... '5-9 ....or format your cells as 'Text'. Hope this helps! In article <64AE5219-035D-46E3-9488-5D01F6B012FA@microsoft.com>, Kicking and Screaming <Kicking and...

Landed Cost on a different currency
Jonder Solomon of Manzo Food Sales Inc, raised that the GP should accommodate to account the added Landed Cost and be able to apply it even when the currency used to pay additional cost is different than the PO. ---------------- 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://...

Pay rate change reasons and date
We need to be able to run a report of all employees that have had a pay change as of a certain date. Could someone tell me the name of the file that holds the change reasons? I've read on Knowledge base that it is stored in the Human Resources Position History but I couldn't find the name of the file. Thanks !! ts, Assuming that you save to history when you change the paycode amount, the data should be in the table HRPSLH01 in your company database. Thanks. Girish "ts" wrote: > We need to be able to run a report of all employees that have had a pay > chan...

working with different operating systems
My computer system information shows that my operating system is Windows 7 Home Premium. I'm wanting to access a web application but it's saying that my computer is showning up to be a Windows Vista Home. According to the requirement to access this web application I need to upgrade my operating system to either a Windows XP Pro or to a Windows Vista Pro. Is there a way to do this with my current operating system? Is there a way I can change my current OS to one of the required OS's? Or, is there a way I can run and/or install one of the required OS's while ...

Chart with two axis
Hi, I have the following data: column A: jan feb mar apr column B: 3 8 5 9 column C: 26 40 45 38 I would like to get a chart where in the horizontal axis I have the monthes and I have 4 points, connected by lines. The first point will have the value 3 in correspondence of the left vertical axis and the value 26 in correspondences of the right vertical axis. .... The fourth point will have the value 9 in correspondence of the left vertical axis and the value 38 in correspondences of the right vertical axis. How can I can do it? Thanks P If I understand what you are asking you want a ch...

Complicated text to date query
HIya, I've inherited a database with three text columns with dates in a text format. In each of these columns the text dates have been added in lots of different formats, e.g. 30409 meaning 03/04/09, so missing the leading zero! or 030409 or 03042009 or 03/04/2009 or 03/04/09 So every way which really... Please help me find an update query, or set of them to get this mish mash of different text formats into a unified short date format in Access 2007. This database is quite large, so no room for manual manipualtions. Thanks Heather I would probably take this in...