473,890 Members | 1,825 Online

# Calculating Time Query

13 New Member
Hi,

I'm having an issue trying to calculate time. Its okay if the value does not exceed 24 hours otherwise I get a date and hours listed. For example, I have a loop which looks through a table and adds up the time spent.

Expand|Select|Wrap|Line Numbers
1. Dim TempCount As Date
2.     Dim DEVCount As Date
3.
4. Do
5.
6.         TempCount = rst!wtime
7.         DEVCount = DEVCount + TempCount
8.         rst.MoveNext
9.         i = i + "1"
10.
11.     Loop Until rst!Anorder = 2
12.
I have two other loops looping at AnOrder 2 and 3 and calculating their totals. They seems to be okay as their total are 9h:25m and 17h:35m. But DEVCount value for anOrder 1 is: 2/1/1900 22:24:00. I guess because its exceeding a 24hour period its adding days? Is there any way of getting a figure like 37h:44m or 57h:24m??

I know a way round it would be to set DEVCount as a date and time and calculate from there but it seems very long winded for what I think should be an easy task.

Feb 28 '08 #1
25 3233
8,834 Recognized Expert Expert
Hi,

I'm having an issue trying to calculate time. Its okay if the value does not exceed 24 hours otherwise I get a date and hours listed. For example, I have a loop which looks through a table and adds up the time spent.

Dim TempCount As Date
Dim DEVCount As Date

Do

TempCount = rst!wtime
DEVCount = DEVCount + TempCount
rst.MoveNext
i = i + "1"

Loop Until rst!Anorder = 2

I have two other loops looping at AnOrder 2 and 3 and calculating their totals. They seems to be okay as their total are 9h:25m and 17h:35m. But DEVCount value for anOrder 1 is: 2/1/1900 22:24:00. I guess because its exceeding a 24hour period its adding days? Is there any way of getting a figure like 37h:44m or 57h:24m??

I know a way round it would be to set DEVCount as a date and time and calculate from there but it seems very long winded for what I think should be an easy task.

If you convert the Time Values to minutes, it then becomes a simple matter. For instance, 1997 minutes would be:
Expand|Select|Wrap|Line Numbers
1. Debug.Print Str\$(Int(1997 / 60)) & "h:" & Trim\$(Str\$(1997 Mod 60)) & "m"
OUTPUT:
Expand|Select|Wrap|Line Numbers
1.  33h:17m
Feb 28 '08 #2
Blaize
13 New Member
I can't get that to work, the data is coming from a query which looks similar to below but more data!

User anOrder Time
A 1 00:15 (15 Minutes)
B 1 15:45 (15 Hours 45 Minutes)
C 1 13:30 (13 Hours 30 Minutes)
D 2 05:00 (5 Hours)
E 2 00:30 (30 Minutes)
F 3 03:00 (3 Hours)

These are the figures I'm trying to get Visual Basic to calculate.

anOrder 1 = 29:30 (29 hours 30 Minutes)
anOrder 2 = 05:30 (5 Hours 30 Minutes)
anOrder 3 = 03:00 (3 Hours)

anOrder 2 and anOrder 3 are fine (at the moment) because they don't exceed 24 hours but anOrder 3 does and I get a date. Is there anyway around this?
Feb 28 '08 #3
Scott Price
1,384 Recognized Expert Top Contributor
Just my personal opinion here, and I'm sure ADezii will come up with a much more spiffy solution :-)

You need to stop treating these as Dates! You have declared your variables in the Date data type, which makes Access, understandably, think they are date values. What you really have, is a String that holds Time data, not a Date that holds Time data.

You will find it much simpler to use some string manipulation functions to split the string down, then concatenate it back.

Regards,
Scott
Feb 28 '08 #4
Blaize
13 New Member
I did think about that but I'm having issues converting, for example one line in the query is "00:15:00" (which is 15 minutes) to an integer value 0.25? If I could do that then I think I can crack it.
Feb 28 '08 #5
Scott Price
1,384 Recognized Expert Top Contributor
Why do you want to convert it to an Integer?

Based on this string: "00:15:00", what output are you looking for? "15 minutes" "0 hours, 15 minutes, 0 seconds"??

Is the time always in this format?

A simple Select query can break the string down and rebuild it in this way:

Expand|Select|Wrap|Line Numbers
1. SELECT tblHOURMINSEC.TimeID, Left([HOURMINSEC],2) & " hours " & Mid([HOURMINSEC],4,2) & " minutes " & Right([HOURMINSEC],2) & " seconds." AS TIMEVAL
2. FROM tblHOURMINSEC;
3.
This takes a value of 00:15:00 and returns an output of "00 hours 15 minutes 00 seconds". It takes 29:34:15 and returns "29 hours 34 minutes 15 seconds".

Regards,
Scott
Feb 28 '08 #6
Scott Price
1,384 Recognized Expert Top Contributor
With the addition of some IIF() functions:

Expand|Select|Wrap|Line Numbers
1. SELECT tblHOURMINSEC.TimeID, IIf(Left([HOURMINSEC],1)="0",Left([HOURMINSEC],1),Left([HOURMINSEC],2)) & " hours " & Mid([HOURMINSEC],4,2) & " minutes " & IIf(Right([HOURMINSEC],2)="00","0",Right([HOURMINSEC],2)) & " seconds." AS TIMEVAL
2. FROM tblHOURMINSEC;
3.
Returns "0 hours 15 minutes 0 seconds". You can tune this as your wish, but it's the general idea.

Regards,
Scott
Feb 28 '08 #7
Blaize
13 New Member
Sorry maybe I'm not being clear, I'm trying to add multiple time figures together to get a total figure.

00:15:00 + 01:15:00 = 01:30

The data i have is split into three sections, Team 1, Team 2 & Team 3. I'm trying to get the total time figure for each Team. This isn't a problem at the moment for Team 2 and Team 3 as the total number only goes up to 11:00 and 07:15. Team 1 should be about 37:45.

The reason why I need to convert it to an integeter is because once I have the total figure I need to divide it by daily hours. This works fine on a Access report I've developed but I now want to do the same thing in VB and export the result to Excel. The report formula is: =Format(Sum([Wtime])/CDate('7.24'),' Fixed')

so another example would be total hours of 14:48 with the above formula would equal: 2.

2 is the figure I'm after so I can populate a spreadsheet. Hope this makes more sense..
Feb 28 '08 #8
Scott Price
1,384 Recognized Expert Top Contributor
That certainly does change the issue! I'll have to give a little more thought on this, and get back to you.

Regards,
Scott
Feb 28 '08 #9
Scott Price
1,384 Recognized Expert Top Contributor
Sorry to be so long getting back to you, Blaize. Today has been a bit hectic.

Here is a quick and dirty function, place it in a standard code module and call it from within a query (or vba code if you like). It assumes an input string of "HH:MM" and returns a Single value corresponding to how many hours and minutes there are. For example, using the strings "00:15" and "29:61" it will return 0.25 and 30.01666 etc.

Expand|Select|Wrap|Line Numbers
1. Option Compare Database
2. Option Explicit
3.
4. Public Function ConvTime(timeIn As String) As Single
5.
6. Dim hours As Integer
7. Dim minutes As Integer
8. Dim convertedTime As Single
9.
10. hours = CInt(Left(timeIn, 2))
11. minutes = CInt(Mid(timeIn, 4, 2))
12.
13.
14. If hours <> 0 Then
15.     convertedTime = hours * 60 + minutes
16. Else
17.     convertedTime = minutes
18. End If
19.
20. ConvTime = convertedTime / 60
21.
22. End Function
23.
To call it from within a query:
Expand|Select|Wrap|Line Numbers
1. Expr1: ConvTime(Left([HOURMINSEC],5))
ADezii probably has a much more spiffy way to do this, as I said earlier :-) However, this is what I could come up with on the spur of the moment.

Regards,
Scott
Feb 29 '08 #10