473,890 Members | 1,825 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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.

Please help!!
Feb 28 '08 #1
25 3233
ADezii
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.

Please help!!
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

Sign in to post your reply or Sign up for a free account.

Similar topics

7
2189
by: JLM | last post by:
I have a table that has fieldA, fieldB, fieldC. I want fieldC=fieldA-fieldB. simple enough. the next record I want to be able to do the same on the new value of fieldC. I can do this with SAP ABAP/4, but have never done this using Access. I'm sure this can be done, but not sure how to go about it. thanks in advance, jlm
1
2381
by: jlm | last post by:
I have a form which feeds table (TblEmpLeave) of Employee Leave Time (time taken off for Administrative, Annual, Sick, Compensation leave). I have EmpID, LeaveDate, LeaveType, LeaveHours fields on this form. Any employee can have multiple entries in the table (key fields are EmpID and LeaveID) for multiple dates (John Doe can take 3 days annual leave, then take 3 days sick leave in any given month. I have a BeginningBalance of hours that...
3
1667
by: Paul Mendez | last post by:
Performance_Date SumOfBudget_NOI CurrYTD_BudgetNOI_Total 1/1/2004 $4,184,626.00 ? 2/1/2004 $4,484,710.00 ? 3/1/2004 $4,537,424.00 ? 4/1/2004 $4,826,850.00 ? 5/1/2004 $4,966,326.00 ? Can someone help? What I am trying to do is create a query that will end up looking like the bottom example. The above query is a calculated totals query with an...
1
4312
by: Tony Williams | last post by:
I have a table with two fields, txtvalue (a number field) and txtmonth ( a date/time field). I want to create a report that shows the difference in value between the value in txtvalue in one value of txtmonth and the value of txtvalue in another value of txtmonth and the percentage increase . For example if I have the value 1000 in 30/03/03 and the value 1100 in 30/03/04 How do I calculate the difference as 100 and the increase as 10%. I...
6
3388
by: Tony Williams | last post by:
SORRY I know we shouldn't do this but I'm desperate for an answer to this and the previous post didn't seem to get a response. I have a table with two fields, txtvalue (a number field) and txtmonth ( a date/time field). I want to create a report that shows the difference in value between the value in txtvalue in one value of txtmonth and the value of txtvalue in another value of txtmonth and the percentage increase . For example if I...
4
2195
by: luscus | last post by:
I am trying to device a formula so that when i check of a yes/no box (done) it will automatically add the time in a field called "End Time" and at the same time stamp the amount of minutes between a field "begining Time" ( which already automatically stamps the time as you enter a new record) and the field "end time" For example : If I begun the new record at 2:45 PM worked on the problem untill 3:00 PM and solved it. I would then...
3
2728
by: luscus | last post by:
Thanks for all the responses on my first question. Unfortunately the answers I was given were too complicated for my small brain , and neophite condition to understand. So if you could talk down to me and write in easier terms I would be very gratefull to all you access wizards! Here is my problem. I have a table with maybe 10 fields, It is used to imput information taken over the phone to solve patient problems in our Spanish...
5
3543
by: Wired Hosting News | last post by:
I tried to be breif and give a scenario so as not to be overlooked because it was soooo long. Let me give you real world. I am a manufacturer of goods and produce 11 items that are distributed to 1800 stores of a national home improvement chain store. Every week I electronicaly receive an excel spreadsheet "inventory report" with 19,800 rows or records, which I import into my tblSalesData table. The table now has 10 weeks of data or...
8
4022
by: King | last post by:
Hi I have following MS Acess query Here is the query ID Name Prgm ID Client ID Date Start Time End Time Minutes C4 Trisha TIP DEK0703 7 /7 /2006 10:00:00 AM 12:00:00 PM 120
0
9812
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
11212
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
10899
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9614
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
8004
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7154
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5832
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
4655
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4255
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.