Calculating Time

  • Thread starter Thread starter Mike
  • Start date Start date
M

Mike

I know this is a bit lengthly, so please be patient.
I am currently running Access 2003 and have a “split†database. I am aware
that Access has a problem with totaling up time that is over 24 hours. So
with the help of a Microsoft Help and Support forum, I was able to create and
use the code that is listed below. And it works great.

Function GetBikePatrolTotal()

Dim db As DAO.Database, rs As DAO.Recordset
Dim totalhours As Long, totalminutes As Long
Dim days As Long, hours As Long, minutes As Long
Dim interval As Variant, j As Integer

Set db = DBEngine.Workspaces(0).Databases(0)
Set rs = db.OpenRecordset("BIKE PATROL")
interval = #12:00:00 AM#
While Not rs.EOF
interval = interval + rs![COMPLETE TOTAL TIME]
rs.MoveNext
Wend
totalhours = Int(CSng(interval * 24))
totalminutes = Int(CSng(interval * 1440))
hours = totalhours Mod 24
minutes = totalminutes Mod 60

GetBikePatrolTotal = totalhours & " hours and " & minutes & " minutes"

End Function

However, what I have noticed is that it will only works with the main table,
which will give me the Total Time since the start of the table which is now
holding about (2) years of information.

I have set up queries to be able to pull Monthly, Yearly, or Quarterly
reports for any of our Tables that are needed.
I am unable to get the above code to work with a query, even with using the
name of the query in place of the name of the table.
The code using the name of the query was looking like this:

Function GetBikePatrolTotal()

Dim db As DAO.Database, rs As DAO.Recordset
Dim totalhours As Long, totalminutes As Long
Dim days As Long, hours As Long, minutes As Long
Dim interval As Variant, j As Integer

Set db = DBEngine.Workspaces(0).Databases(0)
Set rs = db.OpenRecordset("qryBikePatrolByYear")
interval = #12:00:00 AM#
While Not rs.EOF
interval = interval + rs![COMPLETE TOTAL TIME]
rs.MoveNext
Wend
totalhours = Int(CSng(interval * 24))
totalminutes = Int(CSng(interval * 1440))
hours = totalhours Mod 24
minutes = totalminutes Mod 60

GetBikePatrolTotal = totalhours & " hours and " & minutes & " minutes"

End Function

Am I doing something wrong? Or is it just not possible? Since I am new to
VBA and using code, I would not be surprised if I am doing something wrong.


Thank You,
Mike
 
I know this is a bit lengthly, so please be patient.
I am currently running Access 2003 and have a “splitâ€
database. I am aware that Access has a problem with totaling up
time that is over 24 hours. So with the help of a Microsoft Help
and Support forum, I was able to create and use the code that is
listed below. And it works great.

Function GetBikePatrolTotal()

Dim db As DAO.Database, rs As DAO.Recordset
Dim totalhours As Long, totalminutes As Long
Dim days As Long, hours As Long, minutes As Long
Dim interval As Variant, j As Integer

Set db = DBEngine.Workspaces(0).Databases(0)
Set rs = db.OpenRecordset("BIKE PATROL")
interval = #12:00:00 AM#
While Not rs.EOF
interval = interval + rs![COMPLETE TOTAL TIME]
rs.MoveNext
Wend
totalhours = Int(CSng(interval * 24))
totalminutes = Int(CSng(interval * 1440))
hours = totalhours Mod 24
minutes = totalminutes Mod 60

GetBikePatrolTotal = totalhours & " hours and " & minutes & "
minutes"

End Function

However, what I have noticed is that it will only works with the
main table, which will give me the Total Time since the start of
the table which is now holding about (2) years of information.

I have set up queries to be able to pull Monthly, Yearly, or
Quarterly reports for any of our Tables that are needed.
I am unable to get the above code to work with a query, even with
using the name of the query in place of the name of the table.
The code using the name of the query was looking like this:

Function GetBikePatrolTotal()

Dim db As DAO.Database, rs As DAO.Recordset
Dim totalhours As Long, totalminutes As Long
Dim days As Long, hours As Long, minutes As Long
Dim interval As Variant, j As Integer

Set db = DBEngine.Workspaces(0).Databases(0)
Set rs = db.OpenRecordset("qryBikePatrolByYear")
interval = #12:00:00 AM#
While Not rs.EOF
interval = interval + rs![COMPLETE TOTAL TIME]
rs.MoveNext
Wend
totalhours = Int(CSng(interval * 24))
totalminutes = Int(CSng(interval * 1440))
hours = totalhours Mod 24
minutes = totalminutes Mod 60

GetBikePatrolTotal = totalhours & " hours and " & minutes & "
minutes"

End Function

Am I doing something wrong? Or is it just not possible? Since I am
new to VBA and using code, I would not be surprised if I am doing
something wrong.


Thank You,
Mike
Click to expand...
You will probably be better off using a much simpler method to get
your total time, instead of the recordset.

Is [complete total time] a date/time type field?
Use the dateDiff() method to convert it into minutes at each row in a
query, use sum() to get the total number of minutes and then convert
that number of minutes into days, hours, minutes.

In the query, as a calculated field,
Duration: datedif("m",startTime,EndTime)
Sum on this column, group on whatever you wish.

Modify your function to

Function GetBikePatrolTotal(byval DurInMin as long) as string
Dim days As Long, hours As Long, minutes As Long

'Days = DurInMin \ 1440 '60*24
'DurInMin = DurInMin - Days*1440
Hours = DurInMin \ 60
minutes = DurInMin - Hours*60

GetBikePatrolTotal = _
& totalhours _
& " hours and " _
& minutes _
& " minutes"

End Function


on the form or report, set the textbox rowsource to
=GetBikePatrolTotal([sumOfDuration])
 
Thank You for your quick responce Bob.
I will give this a try. With any luck, I will be able to get it to work.
To answer your question,
"Is [complete total time] a date/time type field?"
"Yes, it is". But I only store the Time in military time. The date is not
stored in the same field.
Should it be?

--
Mike


Bob Quintal said:
I know this is a bit lengthly, so please be patient.
I am currently running Access 2003 and have a “splitâ€Â
database. I am aware that Access has a problem with totaling up
time that is over 24 hours. So with the help of a Microsoft Help
and Support forum, I was able to create and use the code that is
listed below. And it works great.

Function GetBikePatrolTotal()

Dim db As DAO.Database, rs As DAO.Recordset
Dim totalhours As Long, totalminutes As Long
Dim days As Long, hours As Long, minutes As Long
Dim interval As Variant, j As Integer

Set db = DBEngine.Workspaces(0).Databases(0)
Set rs = db.OpenRecordset("BIKE PATROL")
interval = #12:00:00 AM#
While Not rs.EOF
interval = interval + rs![COMPLETE TOTAL TIME]
rs.MoveNext
Wend
totalhours = Int(CSng(interval * 24))
totalminutes = Int(CSng(interval * 1440))
hours = totalhours Mod 24
minutes = totalminutes Mod 60

GetBikePatrolTotal = totalhours & " hours and " & minutes & "
minutes"

End Function

However, what I have noticed is that it will only works with the
main table, which will give me the Total Time since the start of
the table which is now holding about (2) years of information.

I have set up queries to be able to pull Monthly, Yearly, or
Quarterly reports for any of our Tables that are needed.
I am unable to get the above code to work with a query, even with
using the name of the query in place of the name of the table.
The code using the name of the query was looking like this:

Function GetBikePatrolTotal()

Dim db As DAO.Database, rs As DAO.Recordset
Dim totalhours As Long, totalminutes As Long
Dim days As Long, hours As Long, minutes As Long
Dim interval As Variant, j As Integer

Set db = DBEngine.Workspaces(0).Databases(0)
Set rs = db.OpenRecordset("qryBikePatrolByYear")
interval = #12:00:00 AM#
While Not rs.EOF
interval = interval + rs![COMPLETE TOTAL TIME]
rs.MoveNext
Wend
totalhours = Int(CSng(interval * 24))
totalminutes = Int(CSng(interval * 1440))
hours = totalhours Mod 24
minutes = totalminutes Mod 60

GetBikePatrolTotal = totalhours & " hours and " & minutes & "
minutes"

End Function

Am I doing something wrong? Or is it just not possible? Since I am
new to VBA and using code, I would not be surprised if I am doing
something wrong.


Thank You,
Mike
Click to expand...
You will probably be better off using a much simpler method to get
your total time, instead of the recordset.

Is [complete total time] a date/time type field?
Use the dateDiff() method to convert it into minutes at each row in a
query, use sum() to get the total number of minutes and then convert
that number of minutes into days, hours, minutes.

In the query, as a calculated field,
Duration: datedif("m",startTime,EndTime)
Sum on this column, group on whatever you wish.

Modify your function to

Function GetBikePatrolTotal(byval DurInMin as long) as string
Dim days As Long, hours As Long, minutes As Long

'Days = DurInMin \ 1440 '60*24
'DurInMin = DurInMin - Days*1440
Hours = DurInMin \ 60
minutes = DurInMin - Hours*60

GetBikePatrolTotal = _
& totalhours _
& " hours and " _
& minutes _
& " minutes"

End Function


on the form or report, set the textbox rowsource to
=GetBikePatrolTotal([sumOfDuration])
Click to expand...
 
Thank You for your quick responce Bob.
I will give this a try. With any luck, I will be able to get it to work.
To answer your question,
"Is [complete total time] a date/time type field?"
"Yes, it is". But I only store the Time in military time. The date is not
stored in the same field.
Should it be?
Click to expand...

If you'll be calculating the time elapsed between values, and especially if
you'll be calculating time elapsed across midnight, then I'd say yes, it
should be.

Access doesn't store dates in "military time" or in any other format. A
Date/Time value is actually a double float number, a count of days and
fractions of a day since midnight, December 30, 1899. A "Time" value is
actually a number between 0 and 1; 0.75 corresponds to the time
#12/30/1899 18:00:00#. A "pure date" field just has nothing after the decimal;
for example today is 39921.000000000 (corresponding to midnight last night).

If you have separate date and time fields, and if you need to cross a midnight
or sort the records chronologically, you must go to extra work to combine the
two fields. Simpler to keep them combined from the beginning!
 
Hi -

Duration: datedif("m",startTime,EndTime) will bomb:
1) It's datediff(
2) Specifying interval as "m" will return datediff in months, use
"n" for minutes.

Bob
Click to expand...

You are absolutely right on both counts, sir. My fingers must have
been still asleep when I posted that early this morning.
Bob said:
I know this is a bit lengthly, so please be patient.
I am currently running Access 2003 and have a
“splitâ€Â
Click to expand...
[quoted text clipped - 67 lines]
Thank You,
Mike
Click to expand...

You will probably be better off using a much simpler method to get
your total time, instead of the recordset.

Is [complete total time] a date/time type field?
Use the dateDiff() method to convert it into minutes at each row
in a query, use sum() to get the total number of minutes and then
convert that number of minutes into days, hours, minutes.

In the query, as a calculated field,
Duration: datedif("m",startTime,EndTime)
Sum on this column, group on whatever you wish.

Modify your function to

Function GetBikePatrolTotal(byval DurInMin as long) as string
Dim days As Long, hours As Long, minutes As Long

'Days = DurInMin \ 1440 '60*24
'DurInMin = DurInMin - Days*1440
Hours = DurInMin \ 60
minutes = DurInMin - Hours*60

GetBikePatrolTotal = _
& totalhours _
& " hours and " _
& minutes _
& " minutes"

End Function

on the form or report, set the textbox rowsource to
=GetBikePatrolTotal([sumOfDuration])
Click to expand...
Click to expand...
 
Bob Q. -

I came on a little bit too strong. For that I apoligize.
Click to expand...
No need to apologise, the corrections are valid, and therefore they
needed to be made.

Think we all, from time to time, suffer from sleepy
fingers.

Appreciate all your great input.

Best Wishes - Bob A.

Bob said:
Hi -
Click to expand...
[quoted text clipped - 4 lines]
Bob
Click to expand...

You are absolutely right on both counts, sir. My fingers must have
been still asleep when I posted that early this morning.
I know this is a bit lengthly, so please be patient.
I am currently running Access 2003 and have a
Click to expand...
[quoted text clipped - 35 lines]
on the form or report, set the textbox rowsource to
=GetBikePatrolTotal([sumOfDuration])
Click to expand...
Click to expand...
Click to expand...
 
Back
Top