Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Aug 2004
    Posts
    40

    Unanswered: How To Calculate Days Elapsed Between Dates?

    I am using Access 2002. I have a query which returns among others a DATE field. I would like to add a new column in my query to the right of my DATE field and name this new field DAYS. The idea is for the DAYS field to calculate and then show the days elapsed between each DATE. I can do this easily with EXCEL but I have no idea how to do this from within my Access query. Please help if you can? Many Thanks in advance.

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    cheat!
    subtract the two dates.
    access dates are days-point-fractionalDays

    ...but the officially documented way is:
    daysDifference = datediff("d", oneDate, anotherDate)

    izy
    currently using SS 2008R2

  3. #3
    Join Date
    Aug 2004
    Posts
    40
    I am sorry but I am still very much confussed. My DATE field returns a list of dates spread over many years. I was hoping that the new DAYS field could automatically look at at each record in turn, and in sequence and automatically calculate the DAYS elapsed between each previous date. The solution that you kindly offered me does not do that? Have you any further suggestions please?

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    the cheat and the "official" method should both work in a query.

    just type your preference of:
    myDiff: datediff("d", [thisDate], [thatDate])
    or
    myDiff: [thisDate] - [thatDate]
    in the top line of the first empty column of the query grid

    ...replacing thisDate & thatDate with whatever the real names are!!

    izy
    currently using SS 2008R2

  5. #5
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    I think Ronald is looking for the date difference between the previous record and the next (not between two date fields).

    So for the following table:
    ID Date
    1 20/8/4
    2 22/8/4
    3 27/8/4

    He wants a query that gives:
    ID Date Elapse
    1 20/8/4 0
    2 22/8/4 2
    3 27/8/4 5

    I might be wrong ! Databases don't like this sort of thing and I can't see an obvious way to do it.

    Chris

  6. #6
    Join Date
    Aug 2004
    Posts
    40
    "CORRECT" you understand and describe my problem perfectly Chris. Thank you very much for your kind assistance. All we have to do now is find someone who can describe a way of achieving that aim? Ronald A. Dixon

  7. #7
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    ive written some code that will do this for a table but don't know if this is any help or not and it just writes values so if your table is reordered it is meaningless

    Code:
    Sub test()
        Dim dbCurrent As Database
        Dim rstTest As Recordset
        
        Dim NoOfRecords As Double, I As Double
        Dim PrevDate As Date, Newdate As Date
        
        'set up object variables
        Set dbCurrent = CurrentDb()
        Set rstTest = dbCurrent.OpenRecordset("tblTest")
        
        'work with recordset
        With rstTest
            .MoveLast
            'count the number of records
            NoOfRecords = .RecordCount
            'go to the first record
            .MoveFirst
            'set first date to 0
            .Edit
            !CalcDiff = 0
            .Update
            For I = 1 To NoOfRecords - 1
                'get the previous date
                PrevDate = .Fields("Datesomething").Value
                .MoveNext
                'get the new date
                Newdate = .Fields("Datesomething").Value
                'update the table with the diffrence of the dates
                .Edit
                !CalcDiff = DateDiff("d", PrevDate, Newdate)
                .Update
            Next I
        End With
        
        'get rid of object variables
        Set rstTest = Nothing
        Set dbCurrent = Nothing
        
    End Sub
    my table is tblTest my dates are in a field called Datesomething and the calulated field is called CalcDiff

    i hope this is of some help

    Dave

  8. #8
    Join Date
    Oct 2003
    Posts
    706
    Quote Originally Posted by RonaldADixon
    I am sorry but I am still very much confussed. My DATE field returns a list of dates spread over many years. I was hoping that the new DAYS field could automatically look at at each record in turn, and in sequence and automatically calculate the DAYS elapsed between each previous date. The solution that you kindly offered me does not do that? Have you any further suggestions please?
    Yes, it does work. If the field is stored in a DateTime data-type, the values can be subtracted.

    Like most databases, Access stores date/time values as a floating point number: the integer part is the number of days since an arbitrary point in time (the "epoch"... doesn't matter what it is but it was a long time ago); the fractional part represents a fraction of the day. Values are translated into this format on input; and back on output. These translations take into account all the vagaries of the calendar (length of months, leap-years), international preferences, and "Y2K ambiguities. (Is "6/12/24" June 12, 1924? December 6th? 1924 or 2024?? The computer decides, on input, and stores the resulting value un-ambiguously.)

    This internal format is expressly designed so that date "arithmetic" can be performed; and with nothing more than "simple math."

    For example, one date-value at 6:00 AM ("one-fourth of a day") might be: 123456.25. A date one-week, twelve hours later would be 123463.75. Internally. But you wouldn't see the value as "how it is stored." You would see it as ... a date and time. Now, "what is the correct time '36 hours later?'" Well, that's 1.5 days later, so add 1.5 to the internal value, decode, and there's your answer. That's what Access does, automagically, for you.


    If your date-values are now stored as character-fields, I'd suggest putting them in a true DateTime field-type, but you can also request this "type-casting" with the Date() {I think that's the right one...} function.
    Last edited by sundialsvcs; 08-27-04 at 09:59.
    ChimneySweep(R): fast, automatic
    table repair at a click of the
    mouse! http://www.sundialservices.com

  9. #9
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    aaaah! well spotted.

    here is a (DAO) recordset idea:

    ???? make a temporary table (tblTemp) that looks like the query you have (qryOrig) plus a field for DaysDiff and it goes something like this:

    'THIS IS DAO CODE!!!!
    'first you probably want to delete everything in tblTemp: up to you!
    'then
    dim dabs as DAO.database
    dim recQ as DAO.recordset
    dim recT as DAO.recordset
    dim LastDate as date
    set dabs = currentdb
    set recQ = dabs.openrecordset(qryOrig)
    set recT = dabs.openrecordset("SELECT * FROM tblTemp;")
    with recQ
    LastDate = !dateField 'EDITED: inconsitent notation with !nameOfYourDateField
    recT.addnew
    recT!thisField = !thisField
    recT!thatfield = !thatField 'etc, then
    recT!DaysDiff = 0 ' there is no difference for the first record
    .movenext
    'then loop thru all the other records
    do while not .EOF
    recT.addnew
    recT!thisField = !thisField
    recT!thatfield = !thatField 'etc ...then...
    recT!DaysDiff = !dateField - LastDate 'or use the official datediff
    recT.update
    LastDate = !datefield
    .movenext
    loop
    end with
    set recT = nothing
    set recQ = nothing
    set dabs = nothing
    'THAT WAS DAO CODE!!!!

    izy
    Last edited by izyrider; 08-27-04 at 10:18.
    currently using SS 2008R2

  10. #10
    Join Date
    Aug 2004
    Posts
    40
    Your solution in code is very much appreciated Dave. Unfortunately, this is all new to me. I do not have the expertise to write my own code as yet and the mechanics of modules and VBA proceedures are still far beyond me just now. However, I do have various VBA handbooks and I have just started to work through them steadily. I was wondering if this solution you offer me will work on the single DATE field in my existing query? and if so how would I do that? Can I copy and paste this code somewhere within my database? Ronald A. Dixon

  11. #11
    Join Date
    Aug 2004
    Posts
    40
    "IZY" many, many thanks, this code of yours looks very good. It is clearly along the the right lines but most of the jargon is over my head. I am a novice. I am only at Level 3 Access database and I have just set out on the SQL and VBA learning curve. Would you (or anyone else for that matter) be kind enough to briefly outline the steps I should take in order to make the best use of your code (and any other coded solutions that I may receive in future) in my existing query?

  12. #12
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    Quote Originally Posted by DavidCoutts
    ive written some code that will do this for a table but don't know if this is any help or not and it just writes values so if your table is reordered it is meaningless....
    As David points out, the code works only on the order in which the dates were entered (and I think izy's code does the same). So if the dates are entered out of chronological order then the results will contain minus numbers. Ronald doesn't mention if this is the case or not but I've changed David's code to always show the difference to the previous most recent date (which is not necessarily the date in the previous record).

    Code:
    Sub test()
    
        Dim dbCurrent As Database
        Dim rstTest As Recordset
        Dim rstTestUnsorted As Recordset
        
        Dim NoOfRecords As Double, I As Double
        Dim PrevDate As Date, Newdate As Date
        
        'set up object variables
        Set dbCurrent = CurrentDb()
        Set rstTestUnsorted = dbCurrent.OpenRecordset("tblTest", dbOpenDynaset)
        
        'create a sorted recordset
        rstTestUnsorted.Sort = "DateSomething"
        Set rstTest = rstTestUnsorted.OpenRecordset
            
        'work with date sorted recordset
        With rstTest
            .MoveLast
            'count the number of records
            NoOfRecords = .RecordCount
            'go to the first record
            .MoveFirst
            'set first date to 0
            .Edit
            !CalcDiff = 0
            .Update
            For I = 1 To NoOfRecords - 1
                'get the previous date
                PrevDate = .Fields("Datesomething").Value
                .MoveNext
                'get the new date
                Newdate = .Fields("Datesomething").Value
                'update the table with the diffrence of the dates
                .Edit
                !CalcDiff = DateDiff("d", PrevDate, Newdate)
                .Update
            Next I
        End With
        
        'get rid of object variables
        Set rstTest = Nothing
        Set rstTestUnsorted = Nothing
        Set dbCurrent = Nothing
    
    End Sub
    To implement (in Access 97 at least):

    First I suggest you work on a copy of your database until you are familiar with code.

    Create a blank form

    Create a button (don't bother with the wizard options)

    In the button properties, click on the "On Click" line.

    Click on the ... (dot,dot,dot) icon to the right of the line

    Select Code Builder

    Paste your code between the Private Sub.. and End Sub lines (don't include the Sub test() and End Sub from the code above.

    Change the references to tblTest and DateSomething to the name of your table and your date field.

    Close the code box.

    Don't forget to create a new field in your table called CalcDiff.

    Save your form.

    Open your form and click on the button.

    Open your table and see the results.

    Make a report or query from the table.

    Note there is no error handling in this code so the code will stop and you will get horrible messages if the code gets to a situation it can't deal with e.g. trying to work on an empty table.

    Good luck

    Chris

  13. #13
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    Ronald:

    where are you stuck:
    getting the code to run?
    making the temp table?
    setting the DAO reference?
    replacing "thisField" with the name of a real field you want in the result?
    or.....?

    i don't want to do the whole thing from 0 -- it's friday night!

    izy
    currently using SS 2008R2

  14. #14
    Join Date
    Aug 2004
    Posts
    40

    RonaldADixon

    IZY of course it is Friday night. By all means go out and enjoy yourself. I have been trying for 10 months locally trying to find a professional person to do this for me without success, I can wait a while longer. I will need it all spelling out I'm afraid but I am most grateful for all the guidance and assistance I have received from everyone in this forum to date. Thanks again. Ronald A. Dixon

  15. #15
    Join Date
    Aug 2004
    Posts
    40
    Following the kind instructions from above. I have created my new blank form based on my existing PastResults table which has the DATE field in it. I have created the button and pasted the code correctly as instructed. I have changed all the references to the name of my table and date field and created a new field called CalcDiff in my tblPastResults. When I open the form and click on the button I get a COMPILE ERROR message which reads: User-defined type not found. On the first line of code dbCurrent As Database is highlighted. At this point I am stuck. Thanks again to all. Ronald A. Dixon

Posting Permissions

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