Query to convert text field to time and find the sum

[sanjit]

field name is TotalHrs & classHrs


here is the query i tried, it doesn't work correctly..
Hope somebody will get me the solution thank you..


SELECT Class_atdn.Name, Count(Class_atdn.Date) AS CountOfDate, Sum(TimeValue(nz([TotalHrs]))) AS SumOfotalHrs, Sum(TimeValue(nz([ClassHrs]))) AS Sum_ClassHrs
FROM Class_atdn
WHERE (((Class_atdn.TotalHrs)>"0") AND ((Class_atdn.ClassHrs)>"0"))
GROUP BY Class_atdn.Name;

[pbaldy]

Can you give examples of what the fields contain?

[sanjit]

TotalHrs	ClassHrs
01:06:00	00:51:00
01:44:00	01:39:00
04:40:00	01:25:00

[ranman256]

in a query:
TotalMins: left(totalHrs,2)*60 + mid(totalHrs,4,2)

ClassMins: left(ClassHrs,2)*60 + mid(ClassHrs,4,2)

now sum these.

[pbaldy]

Don't think the parsing is necessary if they're text:

?timevalue("01:06:00")
1:06:00 AM

[ssanfu]

You are going to have lots of problems because you are confusing TIME with DURATION.

When you write 1:06, you have written a TIME (due to the colon), NOT a duration. (1:06 is 1:06AM)
If you take a college class, say Calculus 101, and you get 3 credit hours, you don't write it 3:00.

You can subtract two times to get a duration (difference between the two times), but you CANNOT add two times to get a meaningful anything.

1:06 is a time (1:05 am)
1hr and 6 minutes is a duration
1.1 hrs is a duration (1hr + (6 minutes/60minutes))

IF you try to add 1:06 and 0:51, you get
#1:06:00# + #0:51:00# , but there is an implied date in a Date/Time field. So you really are trying to add
#10/1/2017 1:06:00# + #10/1/2017 0:51:00#

What do you thing the result will be???
#10/1/2017 1:06:00# (totalhrs)
+ #10/1/2017 0:51:00# (classhrs)
___________________
7/4/2135 1:57:00 AM

Is this what you expected?? Probably not.


Recap: if there is a colon, it is a time........





You could use a Number type field of Single with 2 decimals for the TotalHrs field (1.1 hrs)
and a Number type field of Single with 2 decimals for the ClassHrs field.

Or you could use 2 fields - one for full hours and one for minutes for the TotalHrs and (1 hr and 6 minutes)
and 2 fields - one for full hours and one for minutes for the ClassHrs.
Then do the math to add the minutes (TotalMin+ClassMin)/60 to get additional hours, calculate the remaining minutes, then update the hours and mins fields.

[sanjit]

SELECT tblTimeLog.NAME, Count(tblTimeLog.Log*Date) AS CountOfLogDate, Sum(Left([TotalHrs],2)*60+Mid([TotalHrs],4,2)) AS TotalTime FROM tblTimeLog
WHERE (((tblTimeLog.TotalHrs)>"0"))
GROUP BY tblTimeLog.NAME;

I used the the query this way... it doesn't work. Result is 1650
May b i didn't used the query correctly I am new in access and query so..

[ArviLaanemets]

IF you try to add 1:06 and 0:51, you get
#1:06:00# + #0:51:00# , but there is an implied date in a Date/Time field. So you really are trying to add
#10/1/2017 1:06:00# + #10/1/2017 0:51:00#

Really you are adding
#1/1/1900 1:06:00# + #1/1/1900 0:51:00#