# Thread: How To Calculate Days Elapsed Between Dates?

1. Registered User
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. Cavalier King Charles
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

3. Registered User
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. Cavalier King Charles
Join Date
Dec 2002
Location
Préverenges, Switzerland
Posts
3,740
the cheat and the "official" method should both work in a query.

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

5. Registered User
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. Registered User
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. Registered User
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. Registered User
Join Date
Oct 2003
Posts
706
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 08:59.

9. Cavalier King Charles
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!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!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 09:18.

10. Registered User
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. Registered User
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. Registered User
Join Date
Jul 2004
Location
Southampton, UK
Posts
368
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.

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. Cavalier King Charles
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

14. Registered User
Join Date
Aug 2004
Posts
40

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. Registered User
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
•