468,535 Members | 1,736 Online

# Calculating Time Query

13
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 2718
8,799 Expert 8TB
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 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 Expert 1GB 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 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 Expert 1GB 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 Expert 1GB 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 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 Expert 1GB 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 Expert 1GB 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 ADezii 8,799 Expert 8TB 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. Please help!! Here's a solution, but first a couple of Assumptions: 1. Table Name: tblTimes. 2. [Time] Field is String with Required Property set to Yes. 3. Sample Table Data: Expand|Select|Wrap|Line Numbers 1. ID User Order Time 2. 1 A 1 00:15 3. 2 B 1 15:45 4. 3 C 1 13.30 5. 4 D 2 05:00 6. 5 E 2 00:30 7. 6 F 3 03:00 8. 8 H 7 00:16 4. The following code will create a Recordset based on tblTimes, covert the [Time] Field Values to Minutes, keep a Running Total of the accumulated Minutes, assign the Aggregate Total to a Variable, Format the Aggregate Value in the manner in which you requested, and Print it to the Immediate Window: Expand|Select|Wrap|Line Numbers 1. Dim MyDB As DAO.Database, MyRS As DAO.Recordset 2. Dim intTotalMinutes As Long, intHours As Integer, intMinutes As Integer 3. Dim intRunningMinutes As Integer 4. 5. Set MyDB = CurrentDb() 6. Set MyRS = MyDB.OpenRecordset("tblTimes", dbOpenForwardOnly) 7. 8. Do While Not MyRS.EOF 9. intHours = Val(Left$(MyRS![Time], 2))
10.   intMinutes = Val(Right$(MyRS![Time], 2)) 11. intRunningMinutes = intRunningMinutes + ((intHours * 60) + intMinutes) 12. MyRS.MoveNext 13. Loop 14. 15. intTotalMinutes = intRunningMinutes 16. 17. Debug.Print str$(Int(intTotalMinutes / 60)) & "h:" & Trim$(str$(intTotalMinutes Mod 60)) & "m"
18.
19. MyRS.Close
20. Set MyRS = Nothing
5. Based on the previously displayed data, the OUTPUT is:
Expand|Select|Wrap|Line Numbers
1. 38h:16m
6. Let me know what you think.
7. To Filter the data, create a Query which returns only the desired Records, then create the Recordset based on that Query, and not tblTimes.
Feb 29 '08 #11
Blaize
13
Scott, ADezii, Thank you both so much it works a treat...your both life savers! here's my amended code...
Expand|Select|Wrap|Line Numbers
1. Dim MyDB As DAO.Database, MyQD As DAO.QueryDef, MyRS As DAO.Recordset
2. Dim intTotalMinutes As Long, intHours As Integer, intMinutes As Integer, intNewTotal As Double
3.
4. Dim intRunningMinutes As Integer
5.
6.     Set MyDB = CurrentDb()
7.     Set MyQD = MyDB.QueryDefs!qryDailyDevStats_Output
8.
9.     MyQD.Parameters![Pram1] = Now()
10.
11.     Set MyRS = MyQD.OpenRecordset
12.
13.     Do While Not MyRS.EOF
14.         intHours = Val(Left$(MyRS![wtime], 2)) 15. intMinutes = Val(Mid$(MyRS![wtime], 4, 2))
16.         intRunningMinutes = intRunningMinutes + ((intHours * 60) + intMinutes)
17.         MyRS.MoveNext
18.     Loop
19.
20.     intTotalMinutes = intRunningMinutes
21.
22.     Debug.Print Str$(Int(intTotalMinutes / 60)) & "h:" & Trim$(Str$(intTotalMinutes Mod 60)) & "m" 23. 24. intNewTotal = CDec((intTotalMinutes / 60)) 25. 26. intNewTotal = CDec((intNewTotal / 7.4)) 27. 28. MyRS.Close 29. Set MyRS = Nothing Feb 29 '08 #12 ADezii 8,799 Expert 8TB Scott, ADezii, Thank you both so much it works a treat...your both life savers! here's my amended code... Expand|Select|Wrap|Line Numbers 1. Dim MyDB As DAO.Database, MyQD As DAO.QueryDef, MyRS As DAO.Recordset 2. Dim intTotalMinutes As Long, intHours As Integer, intMinutes As Integer, intNewTotal As Double 3. 4. Dim intRunningMinutes As Integer 5. 6. Set MyDB = CurrentDb() 7. Set MyQD = MyDB.QueryDefs!qryDailyDevStats_Output 8. 9. MyQD.Parameters![Pram1] = Now() 10. 11. Set MyRS = MyQD.OpenRecordset 12. 13. Do While Not MyRS.EOF 14. intHours = Val(Left$(MyRS![wtime], 2))
15.         intMinutes = Val(Mid$(MyRS![wtime], 4, 2)) 16. intRunningMinutes = intRunningMinutes + ((intHours * 60) + intMinutes) 17. MyRS.MoveNext 18. Loop 19. 20. intTotalMinutes = intRunningMinutes 21. 22. Debug.Print Str$(Int(intTotalMinutes / 60)) & "h:" & Trim$(Str$(intTotalMinutes Mod 60)) & "m"
23.
24.     intNewTotal = CDec((intTotalMinutes / 60))
25.
26.     intNewTotal = CDec((intNewTotal / 7.4))
27.
28.     MyRS.Close
29.     Set MyRS = Nothing
Glad Scott and I got it working for you. Remember to keep three things in mind, or else the code will not function properly:
1. Your Time Field, [wtime], must be a String Data Type.
2. Your Time Field, [wtime], must be in the Format hh:mm.
3. Your Time Field, [wtime], cannot contain a Null Value, so either set the Required Property to Yes in the Table, or set a Criteria in the Query of Not Is Null, and base the Recordset on it.
4. Nice job of following through and arriving at a solution using two alternative approaches.
5. Thanks Scott!
Feb 29 '08 #13
Blaize
13
Have got one final issue though! Works fine at the moment in development as a MDB file. I've compliled and saved then created an MDE file for distribution and get the error: "The expression On Click you enetered as the event property setting produced the following error: The expression may not result in the name of a macro, the name of a user-defined fuction or [Event Procedure]. + There may have been an error evaluating the function, event, or macro"??

I've narrowed it down and it seems to fall over on this line:

MyQD.Parameters![Pram1]=Now()

But i can see anything wrong with it? Like I say it would fine when you run it as an MDB file. I've changed it to a string as I thought it was an issue with the 'Now()' function

Any Idea's?
Mar 3 '08 #14
Scott Price
1,384 Expert 1GB
ADezii is on vacation for a few days :-)

Now() is an intrinsic VBA function that returns an integer value of the current date and time. VBA shouldn't have any trouble evaluating it, but you never know :-)

Can you see a way to restructure the code to do away with that part? I'm a bit busy right now, and don't have the time to test it through myself, but I'd say you might need to restructure that part of the code. I'll try to snatch a minute later on today to take a closer look at it.

Regards,
Scott

what version of Access are you using? Can't remember if you've said yet...
Mar 3 '08 #15
NeoPa
32,099 Expert Mod 16PB
Try adding this line before line #9.
Expand|Select|Wrap|Line Numbers
1. Call MsgBox(Now())
When you convert this to an MDE & run it, see which line it complains about.
Mar 3 '08 #16
Blaize
13
What I've done is to comment out all the code and this is the line it falls over at. I've never had this issue before I've checked my references and DAO and others are listed, like I say everything works perfectly as long as you don't generate an MDE file, really weird!? Problem is it needs to be built as an MDE file as 30-35 people will be using it... nuts!
Mar 3 '08 #17
NeoPa
32,099 Expert Mod 16PB
Sorry Blaise, is this a response to my last post or did you not see it?
Mar 3 '08 #18
Blaize
13
Hi NeoPa,

I've using Visual Basic with Access 2000 so Code:(Text) does not work. I tried your other and it bring a message box with the date and time, so not sure what thats trying to resolve? I know the line which causes the error message its the line where I'm passing a parameter to a query, which is line 9. Just wonder if its something simple which I've missed that the complier isn't picking up??

Cheers,
James
Mar 4 '08 #19
NeoPa
32,099 Expert Mod 16PB
OK James.
A couple of things to clear up.
1. Code:(Text) is simply the line which lets the reader know that the following text is code. The code lines themselves are numbered. The code line I was referring to was Call MsgBox(Now()).
2. I was able to understand your earlier post explaining the problem occurred in line #9. I designed a line of code that would give even clearer understanding of what is going wrong.
If you leave your code as it was when you last posted it, but add :
Call MsgBox(Now())
... as a separate line before line #9, then we can determine from the results whether or not the function Now() is the problem or not. Refer back to post #16 for full instructions and let us know what occurrs.
Mar 4 '08 #20
Blaize
13
Hi,

Nah its not the Now() function which is causing the issue I've replaced Now() with todays date. Compiled, saved works fine as a MDB file, build to an MDE file and get: "The expression On Click you entered..." error. Tried loads of things to get round it, even a query with all the 'If' statements etc and still get the same error. Either something really basic or critically wrong which is causing it to fail. What I can't get over is the fact that it works perfectly well as a MDB file.

Cheers,
James
Mar 4 '08 #21
Blaize
13
...but in answer to your question, adding in the line of code Call MsgBox(Now()) it displays the date & time, click on, then you get the error message,

Cheers,
James
Mar 4 '08 #22
Scott Price
1,384 Expert 1GB
This thread indicates that using a DAO recordset with a connected backend may require that you specifically open the backend first, before running the rest of your code.

The specific command given is

Expand|Select|Wrap|Line Numbers
1. set db = dbengine(0).opendatabase("c:\foo\bar\mydata.mdb")
Obviously you'll need to change the c:\ path to reflect your database' location.

Regards,
Scott
Mar 4 '08 #23
NeoPa
32,099 Expert Mod 16PB
...but in answer to your question, adding in the line of code Call MsgBox(Now()) it displays the date & time, click on, then you get the error message,

Cheers,
James
Ah, an answer to the question. Just what's required.

The MDE file is NOT the same as an MDB file. The rules are different. I'm not sure what they all are as I never use MDEs, but at least we now know where to look (your other tests provided little or no clues in that area).

I will need to revisit this later as I'm running late already and need to get home. Bump the thread tomorrow if I don't post something later tonight though. If I can't sort it then I'm sure we can find someone who can now we know where the issue lies.

PS. It looks as if Scott has already come up with something. let us know how you fare with this.
Mar 4 '08 #24
Blaize
13
I realise that MDE and MDB files are not the same but in terms of comiling a database you would think that if something is working as a MDB file that when you come to create an MDE file it should also work.

The query I created is held within the database whre I have the code. The backend has already connected. I did how ever add the command to test and I still get the same issue. I can't beleive such a straight forward command is causing so much of a headache?! I'm sure I've mentioned this before but I'm using Access 2000. The parameter I'm passign to the query is a Date value.
Mar 7 '08 #25
NeoPa
32,099 Expert Mod 16PB
I realise that MDE and MDB files are not the same but in terms of comiling a database you would think that if something is working as a MDB file that when you come to create an MDE file it should also work.
...
I wasn't trying to differentiate between the files, but between how (and if) the compiled project works.
You are right in that one would like to hope that they would behave in a consistent manner in both forms. However, the unfortunate fact is that they don't. There are rules to follow when converting an MDB to an MDE. You should find these explained in your help system. Never having used an MDE myself, I'm not sure what the rules are, I'm simply aware they exist.

This is all a bit similar to what happens when you upgrade from one version to another. There should be compatibility. In most cases there is not.
Mar 7 '08 #26