1

I am creating a database for the purpose of calculating the length of time that had passed between two milestones. 

[ID]    [Milestone] [MDate]     [Length of Time]
--------------------------------------------------------
  1    1        Jan 1, 2013 [If the following milestone is 2 and they have both the same ID;
                     Calculate Datediff of Jan 1, 2013 vs Feb 1, 2013] Ans: 1 month]

  1    2        Feb 1, 2013 [if no milestone that followed, end date should be date();                          Datediff= Feb 1, 2013 vs. Date()]

  2    1            Jan 15, 2013    [If the following milestone is 2 and they have both the same ID;
                     Calculate Datediff of Jan 15, 2013 vs Feb 1, 2013] Ans: 18 days]

  2    2        Feb 1, 2013 [If the following milestone is 3 and they have both the same ID;
                     Calculate Datediff of Feb 1, 2013 vs Feb 27, 2013] Ans: 12 days]

  2    3        Feb 27, 2013    if no milestone that followed, end date should be date();                           Datediff= Feb 27, 2013 vs. Date()]

The setup of the table is:

[ID] - Number; 
[Milestone] - Number; 
[Mdate]-Date; 
[Length of time] - calculated field in the query; should be with a user defined function

The problem is I cannot automate the calculation of length of time between the two. I need my database to sense that when two records with the same ID and a milestone number was changed, the previous milestone will use the end date of the following milestone, else if nothing follows the end date is current date.

I need a user defined function that automatically calculate the length of time.

Peter Albert
  • 16,917
  • 5
  • 64
  • 88
buyoy
  • 11
  • 1
  • Do you have to store the Date differente in your table instead of the Terminal Date? Do you have to store information about the end of the Milestone at all? Anyway, the Datediff Function should Meet your needs, See [here](http://stackoverflow.com/questions/8221148/difference-between-dates-vba). – collapsar Mar 07 '13 at 09:15

0 Answers0