473,320 Members | 2,020 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

query sun of time fields

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
19 3178
oooooooooookkkkkkkkkkkkkkkk
Nov 17 '06 #2
MMcCarthy
14,534 Expert Mod 8TB
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
32,556 Expert Mod 16PB
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
14,534 Expert Mod 8TB
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
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
14,534 Expert Mod 8TB
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
It Worked

Thanks
Nov 20 '06 #8
MMcCarthy
14,534 Expert Mod 8TB
It Worked

Thanks
No Problem
Nov 20 '06 #9
NeoPa
32,556 Expert Mod 16PB
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
14,534 Expert Mod 8TB
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
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
14,534 Expert Mod 8TB
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
32,556 Expert Mod 16PB
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
Killer42
8,435 Expert 8TB
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
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
Killer42
8,435 Expert 8TB
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
Killer42
8,435 Expert 8TB
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
32,556 Expert Mod 16PB
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
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

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

Similar topics

6
by: Martin Lacoste | last post by:
Ok, before I headbutt the computer... don't know why when I add criteria in a query, I get an 'invalid procedure call'. I also don't know why after searching the help in access, the various access...
6
by: Brian | last post by:
Hello, Basically, I'm running a query on a form's activation, and I'd like to have the results of the query be placed into other fields on the same form automatically. Does anybody know how...
8
by: Maxi | last post by:
There is a lotto system which picks 21 numbers every day out of 80 numbers. I have a table (name:Lotto) with 22 fields (name:Date,P1,P2....P21) Here is the structure and sample data: ...
2
by: rivka.howley | last post by:
I wrote some code that creates a table with a date/time field at 15-minute intervals. Here's how I create and populate the table With tblDataTemp ..Fields.Append .CreateField("CT_ID", dbLong)...
4
by: Macroman | last post by:
MS Access XP, running on Win XP, Processor 2.4Ghz , 512Mb RAM, 40Gb Hard drive Table 1 has 167,000 records and contains the following fields tblone_custID tblone_easting tblone_northing ...
1
by: commodityintelligence | last post by:
Greetings, I am merging a series of different tables into one query to export decision-making information. I have some architecture issues I need to ask for help on. I have no programming...
4
by: Doris | last post by:
It does not look like my message is posting....if this is a 2nd or 3rd message, please forgive me as I really don't know how this site works. I want to apologize ahead of time for being a novice...
5
by: DeanL | last post by:
Hi all, I'm trying to set up a query that runs from a command button on a form (simple enough so far), what I want the query to do is take values from the fields on the form (seven fields in...
3
by: Richard Hollenbeck | last post by:
I am very sorry about the (almost) re-post, but you will see that my first question wasn't very clear; I have another question I posted this morning called, "in DAO: Run time error 3061 Too few...
9
by: Sinner | last post by:
Hi, I have a field name 'USER' in tableMAIN. How do I replace the user names with corresponding user names. I can do that in xl using vlookup but now I'm trying to find a way to do that in...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

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.