By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,462 Members | 2,308 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,462 IT Pros & Developers. It's quick & easy.

query sun of time fields

P: 4
I'm new to acess and working on my first db. I haven't started using code yet so i still working with wizards. I am using Acess 2000 on an Windows XP.
My problem is i have a time field that i need too run a query on and the sum needs to be in hundreds of hours but i've tried everything and as soon as the sum gets to 23:59 it starts over at 0:00. I need this to show large hours numbers
Nov 17 '06 #1
Share this Question
Share on Google+
19 Replies


P: 4
oooooooooookkkkkkkkkkkkkkkk
Nov 17 '06 #2

MMcCarthy
Expert Mod 10K+
P: 14,534
I'm new to acess and working on my first db. I haven't started using code yet so i still working with wizards. I am using Acess 2000 on an Windows XP.
My problem is i have a time field that i need too run a query on and the sum needs to be in hundreds of hours but i've tried everything and as soon as the sum gets to 23:59 it starts over at 0:00. I need this to show large hours numbers
Try this:

Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT Field1, Field2, 
  3. CInt(Sum([TimeField])) * 24 +  Hour(Sum([TimeField]) As TotalHours,
  4. Minute(Sum([TimeField])) As Mins
  5. FROM Tablename
  6. GROUP BY Field1, Field2;
  7.  
  8.  
Nov 17 '06 #3

NeoPa
Expert Mod 15k+
P: 31,186
Or a slightly modified version :
Expand|Select|Wrap|Line Numbers
  1. SELECT Field1, Field2, 
  2.     Int(Sum([TimeField])) * 24 + Hour(Sum([TimeField]) As TotalHours, 
  3.     Minute(Sum([TimeField])) As Mins
  4. FROM Tablename
  5. GROUP BY Field1, Field2
Not just for fun - CInt rounds as it goes. Int() truncates, as is required here.
Nov 17 '06 #4

MMcCarthy
Expert Mod 10K+
P: 14,534
Or a slightly modified version :
Expand|Select|Wrap|Line Numbers
  1. SELECT Field1, Field2, 
  2. Int(Sum([TimeField])) * 24 + Hour(Sum([TimeField]) As TotalHours, 
  3. Minute(Sum([TimeField])) As Mins
  4. FROM Tablename
  5. GROUP BY Field1, Field2
Not just for fun - CInt rounds as it goes. Int() truncates, as is required here.
Believe it or not, I originally had it as an Int but changed my approach a couple of times and ended up with CInt. I never remember the rules anyway. Have to go look them up on a regular basis. My mind is like a sieve.

Mary
Nov 17 '06 #5

P: 4
Thanks Neopa and mmccarthy
Iím still having trouble with this one. I never used code before so be patient. After this database is finished this week Iíll learn more about code.
I have a table called [PTP Vehical daily log] (donít mind the misspelling)
I have three fields in it [start time], [end time], and [night time].
I need to subtract the [start time] from the [end time] then add the [night time] to this total.
Finally I need a grand total in hours that will not roll over at 23:59 like it does.
I tried to follow your example but I keep getting a Ďsnytax error (missing operative)í.What I came up with is below.
I would appreciate any help you can give and I promise if I can get this working I wont bother you anymore!!!!!!!!!!!


SELECT
CInt(Sum(([PTP Vehical daily log]![end time]-[PTP Vehical daily log]![start time])+[PTP Vehical daily log]![night time]) * 24 + Hour(Sum(([PTP Vehical daily log]![end time]-[PTP Vehical daily log]![start time])+[PTP Vehical daily log]![night time])) AS TotalHours, Minute(Sum(([PTP Vehical daily log]![end time]-[PTP Vehical daily log]![start time])+[PTP Vehical daily log]![night time])) AS Mins
FROM [PTP Vehical daily log]
GROUP BY [PTP Vehical daily log].[start time], [PTP Vehical daily log].[end time], [PTP Vehical daily log].[night time]
Nov 20 '06 #6

MMcCarthy
Expert Mod 10K+
P: 14,534
OK try this, the group by is only needed if you want your results broken out over something like EmployeeID. I think the main problem was a misplaced bracket.

Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT (Int(Sum(([end time] - [start time]) + [night time])) * 24 
  3. + Hour(Sum(([end time] - [start time]) + [night time])) AS TotalHours, 
  4. Minute(Sum(([end time] - [start time]) + [night time])) AS Mins
  5. FROM [PTP Vehical daily log];
Nov 20 '06 #7

P: 4
It Worked

Thanks
Nov 20 '06 #8

MMcCarthy
Expert Mod 10K+
P: 14,534
It Worked

Thanks
No Problem
Nov 20 '06 #9

NeoPa
Expert Mod 15k+
P: 31,186
Expand|Select|Wrap|Line Numbers
  1. SELECT Sum([end time] - [start time] + [night time]) AS TotTime, _
  2.     Int(TotTime) * 24 + Hour([TotTime]) AS TotalHours, _
  3.     Minute([TotTime]) AS Mins
  4. FROM [PTP Vehical daily log];
Bear in mind, unless you want this across the whole recordset, you'll need a GROUP BY clause.
This is fundamentally the same as Mary's code.
The advantage is the complicated field is worked out just the once (in the code - Mary's would also be worked out once at execution) and then reused more ligibly. It also introduces a useful concept available in SQL.
I hope this makes sense.
Nov 20 '06 #10

MMcCarthy
Expert Mod 10K+
P: 14,534
Expand|Select|Wrap|Line Numbers
  1. SELECT Sum([end time] - [start time] + [night time]) AS TotTime, _
  2. Int(TotTime) * 24 + Hour([TotTime]) AS TotalHours, _
  3. Minute([TotTime]) AS Mins
  4. FROM [PTP Vehical daily log];
Bear in mind, unless you want this across the whole recordset, you'll need a GROUP BY clause.
This is fundamentally the same as Mary's code.
The advantage is the complicated field is worked out just the once (in the code - Mary's would also be worked out once at execution) and then reused more ligibly. It also introduces a useful concept available in SQL.
I hope this makes sense.
Good idea Adrian

Mary
Nov 20 '06 #11

P: 59
OK try this, the group by is only needed if you want your results broken out over something like EmployeeID. I think the main problem was a misplaced bracket.

Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT (Int(Sum(([end time] - [start time]) + [night time])) * 24 
  3. + Hour(Sum(([end time] - [start time]) + [night time])) AS TotalHours, 
  4. Minute(Sum(([end time] - [start time]) + [night time])) AS Mins
  5. FROM [PTP Vehical daily log];
I have similar problem.
Endtime sometimes pass midnight and in this case it is less then starttime.
How can I calculate totalhours and grandtotal hours in query
Nov 23 '06 #12

MMcCarthy
Expert Mod 10K+
P: 14,534
I have similar problem.
Endtime sometimes pass midnight and in this case it is less then starttime.
How can I calculate totalhours and grandtotal hours in query
Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT Sum(IIf([end time] > [start time], [end time] - [start time],
  3. (#24:00# - [start time]) + [end time]) As TotalHours
  4. FROM TableName;
  5.  
I think I have the logic of this right. Check it out and let me know.

Mary
Nov 23 '06 #13

NeoPa
Expert Mod 15k+
P: 31,186
Not really.
Actually, the difference between two Date/Times is the same regardless of whether or not midnight comes between them or not.
The original code works fine.
Nov 24 '06 #14

Expert 5K+
P: 8,435
Not really.
Actually, the difference between two Date/Times is the same regardless of whether or not midnight comes between them or not.
The original code works fine.
I can't believe I only just spotted this thread! :) Itís so close to what I have been trying to get right lately.

One question, though - the whole logic of adding [NightTime] seems suspicious to me. Is that another date/time field, or just a number of hours? If the latter, then OK. If the former, then the whole concept seems invalid.

Consider the actual values in some date/time fields. If we take some arbitrary values...
Expand|Select|Wrap|Line Numbers
  1. Field        Value            Numeric          _
  2. StartTime    #1/1/2006 10:00:00 AM#    38718.4166666667
  3. EndTime        #1/1/2006 3:00:00 PM#    38718.625
  4. NightTime    #1/1/2006 10:00:00 PM#    38718.9166666667
  5.  
  6. EndTime Ė StartTime + NightTime = 38719.125
Depending on how you want to display it, thatís either 03:00 the following morning, or just under one million hours.
Nov 24 '06 #15

P: 59
Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT Sum(IIf([end time] > [start time], [end time] - [start time],
  3. (#24:00# - [start time]) + [end time]) As TotalHours
  4. FROM TableName;
  5.  
I think I have the logic of this right. Check it out and let me know.

Mary
When I run this code return me message "Data type mismatch in criteria expression"
Nov 24 '06 #16

Expert 5K+
P: 8,435
When I run this code return me message "Data type mismatch in criteria expression"
What are the types of all the fields?
Nov 24 '06 #17

Expert 5K+
P: 8,435
When I run this code return me message "Data type mismatch in criteria expression"
I've just been playing with this in Access 2000, and it seems to feel that #24:00# is an "invalid date value".

As somebody else here (NeoPa, I htink) pointed out, date/time fields include both date and time, so there's no need to worry about the start time being "higher". You can probably just do the subtraction and skip the 24 hour thing.

I hope. :)
Nov 24 '06 #18

NeoPa
Expert Mod 15k+
P: 31,186
Killer,

[EndTime]-[StartTime] can be considered to be a Delta - or difference - measured in Days and Hours, whereas [NightTime] is a full historical Date/Time field.

Your difficulty in displaying your values was because Format() is not designed to display Deltas properly for dates. The time part isn't too bad, but it will always try to display anything greater than a day as an actual date in history. Arithmetic using Deltas should work fine, but there can only be one historical date in the formula if it's to make sense (2 historicals - 1 historical is fine but the sum should be 1 if you get my meaning). This isn't too complicated a concept BUT it throws a lot of people because they're so used to dealing with dates as historical dates that they get confused by the idea of Deltas.

I hope this helps to clarify.
Nov 24 '06 #19

P: 59
I've just been playing with this in Access 2000, and it seems to feel that #24:00# is an "invalid date value".

As somebody else here (NeoPa, I htink) pointed out, date/time fields include both date and time, so there's no need to worry about the start time being "higher". You can probably just do the subtraction and skip the 24 hour thing.

I hope. :)
you are right, when date/time field include both date and time problem is solved.
Thanks,
Nov 24 '06 #20

Post your reply

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