Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Join Date
    Jul 2005
    Posts
    30

    Unanswered: Difference between two dates

    This is a problem that has bugged me for a long time. How do you calculate the difference between consecutive dates in Access when you dont have a beginning and ending date. Date diff works pretty easily when you have two fields and excel does this easily. Is there a way to do this with vba?
    Table is very basic. Fields are name, date, jobtype. I need the number of days between the dates. Any suggestions would be helpful. I inherited the database so rewriting it with a different design is out of the question.

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    To calculate on a date, you need at least one date (whether it's today's date using the date() or 1 or 2 dates from the table.) DateAdd and DateDiff calculate the difference based on the date supplied, what you want to calculate on (i.e. "w", "d", "y", etc..) and how you want to calculate the date in the function but somewhere you need to supply "A" date.

    Note: Hopefully you don't have a field name just called: Date. Please change this if you do to something like DateOf...or DateEntered or DateUpdated or UpdatedDate...something other than just Date. You'll save yourself a LOT of pain not using Access keywords as field names. The same if you have a field name called: Name.
    Last edited by pkstormy; 11-01-07 at 11:24.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    Are you just needing the difference between the first date in the table and the second date, then the diff of 2nd date and 3rd date, and then 3rd date and 4th date? If so, try this SQL.
    Code:
    SELECT Table3.MyDate, DateDiff("d",(SELECT Max(tbl3.MyDate) AS MaxOfMyDate 
    FROM Table3 as tbl3 
    WHERE MyDate < Table3.MyDate;),[Table3].[MyDate]) AS DateDiff
    FROM Table3
    ORDER BY Table3.MyDate;
    Of course you will need to change Table3 to the name of your table, and MyDate to the name of your date field.

    I did not see Paul's response until after I posted mine, but I agree with what he says 100%. That's why I used MyDate in my example, rather than Date as you stated in your request. As long as you use Access reserved words, you will need to put them in brackets "[]" when using them.

  4. #4
    Join Date
    Nov 2003
    Location
    Sussex, England
    Posts
    404
    The same if you have a field name called: Name
    I remember being caught with that one years ago. The pain will never go away!!


  5. #5
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Quote Originally Posted by Ryker
    I remember being caught with that one years ago. The pain will never go away!!

    I was caught also! And unfortunately it was too late as I had about 20 other different databases (and lot's of code) all which utilized the business table which I idiotically called a field: Name. It's been over 10 years now and you're 100% right in that the pain never really goes away.
    Last edited by pkstormy; 11-01-07 at 12:24.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  6. #6
    Join Date
    Jul 2005
    Posts
    30
    [QUOTE=darejamr]This is a problem that has bugged me for a long time. How do you calculate the difference between consecutive dates in Access when you dont have a beginning and ending date. Date diff works pretty easily when you have two fields and excel does this easily. Is there a way to do this with vba?
    Table is very basic. Fields are name, date, jobtype. I need the number of days between the dates. Any suggestions would be helpful. I inherited the database so rewriting it with a different design is out of the question

  7. #7
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Quote Originally Posted by darejamr
    How do you calculate the difference between consecutive dates in Access when you dont have a beginning and ending date.
    To do any date calculations, you need to have at least 1 date! You can't calculate on a fathom date. So the question is, what date are you trying to calculate on (i.e. today's date, a date from the table?) This statement is very difficult to understand. Please re-word.


    Quote Originally Posted by darejamr
    I need the number of days between the dates.
    Ok. Date1-Date2 will give you the # days between date1 and date2. You can also utilize the dateadd function if you're working with 1 date field but tell us what dates you are trying to calculate from. Are you trying to calculate the date from 2 different records dependent upon the date field in each record? I'm having a difficult time understanding what you're asking with the information you supplied.

    For example:

    Name, Date, SomeOtherField
    xxxx, 1/1/07, dfkslfdkl
    yyyy, 12/31/07, dfskfksd;f

    Are you trying to calculate the # of days between date fields for record yyyy and xxxx?
    Last edited by pkstormy; 11-01-07 at 13:26.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  8. #8
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    darejamr,
    Did you just repost your first request because you thought we were not answering your question the way you asked it? If so, please respond to what answers have been given, and what about them is correct or not correct for what you want. Otherwise, as Paul just stated, we have no idea what you want beyond what has been supplied already.

  9. #9
    Join Date
    Jul 2005
    Posts
    30
    Ok maybe I was unclear. I have a table of machine failures by name, date, type, and a few other fields. Each date is the date the machine was put back on line after a breakdown. The on line time for the machine is what's important.The difference between dates is the days up time on the machine. Dates start at about 2002 for many machines. So what I am tryin to do is determine the number of day's the machine ran before breakdown by subtracting the dates in the table from each other. Dates are in descending order. I want to subtract the first date from the second, second from the third and so on. Haven't been able to find any help anywhere on this. I have to believe that this must be a common problem. Hope this helps. I appreciate the quick responses and apologize if i was unclear in my initial post.

  10. #10
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    That is what I thought I gave you in the SQL code I left at 7:30 this morning. Have you tried it? If so what happens?

  11. #11
    Join Date
    Jul 2005
    Posts
    30
    Vic: Tried this code.
    SELECT well_work_tb.[well name], well_work_tb.WELL_WORK_DTE, DateDiff("d",(SELECT Max( tbl3.well_work_dte) AS MaxOfwell_work_dte
    FROM WELL_WORK_tb as tbl3
    WHERE well_work_dte < well_work_tb.well_work_dte,well_work_tb.well_work_dte) AS DateDiff
    FROM well_work_tb
    WHERE (((well_work_tb.WELL_WORK_DTE)>=1/#1/1/2007#))
    ORDER BY well_work_tb.[well name] DESC , well_work_tb.WELL_WORK_DTE DESC;
    Returned these results.
    well name WELL_WORK_DTE DateDiff
    LBC028 2/21/2007 1
    LBC028 1/27/2005 4
    LBC028 9/15/2004 2
    LBC028 3/30/2004 1
    LBC028 2/17/2003 3
    LBC028 12/13/2002 3
    LBC028 7/31/2002 6
    LBC028 4/10/2002 1
    LBC028 10/12/2001 1

  12. #12
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    You can make things soo much easier on yourself if you just had a StartDate and EndDate field in the table verses one date field. This is usually the way this is done. Then you wouldn't have to worry about code to loop through the recordset (which is ultimately what you'll need to do) and can instead do this easily in 1 query where you have an expression: DDiff: StartDate - EndDate.
    Here's an example... - see query1.
    Attached Files Attached Files
    Last edited by pkstormy; 11-01-07 at 15:23.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  13. #13
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    OK, you showed us what happened. Is that what you want or not? If not, what is wrong with what you got?

  14. #14
    Join Date
    Jul 2005
    Posts
    30

    Smile

    Vic: Your sql code works great!. Just had to put it into a subquery to get it to work right. Now outputting days between dates like a champ. Thanks for the help. Solves a big problem for me.

  15. #15
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    Thanks for the feedback. It's always good to know something is working.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •