jkasavan
asked on
Microsoft Query Functions
I have a pivot table based on an Access file of trucks weighing in and out of a facility. The data includes the time the truck weighs in, and then (after it is loaded or unloaded) the time it weighs out. I want to determine the elapsed time using MS Query and then return the data to Excel.
Here is a little bit of the data:
GTime TTime ElapsedTime
07:42 07:14 28
14:33 15:11 39
19:52 20:33 41
17:25 18:29 64
09:09 09:53 45
12:20 11:53 28
Does MS Query have built in date/time formats that I can use to create a calculated column like this:
(GTime - TTime) = Elapsed
or something like
timevalue(GTime) - timevalue(TTime) = Elapsed
I got it to work when I did this:
abs(left(GTime,2)*60+right (Gtime,2) - left(TTime,2)*60+right(TTi me,2))
I am looking for information about time/date functions that are existing in MS Query. I have spent hours on Google trying to find The Definitive Guide to MS Query, but there does not appear to be any good reference material on it. Anyone know where such a book exists?
(One of you experts would make a ton of dough by writing a book like that.)
Here is a little bit of the data:
GTime TTime ElapsedTime
07:42 07:14 28
14:33 15:11 39
19:52 20:33 41
17:25 18:29 64
09:09 09:53 45
12:20 11:53 28
Does MS Query have built in date/time formats that I can use to create a calculated column like this:
(GTime - TTime) = Elapsed
or something like
timevalue(GTime) - timevalue(TTime) = Elapsed
I got it to work when I did this:
abs(left(GTime,2)*60+right
I am looking for information about time/date functions that are existing in MS Query. I have spent hours on Google trying to find The Definitive Guide to MS Query, but there does not appear to be any good reference material on it. Anyone know where such a book exists?
(One of you experts would make a ton of dough by writing a book like that.)
There does not appear to be any correlation between the start and end times and your two GTime and TTime fields. I would have expected one to be always greater than the other. Do you ever have a situation where a before time is just before midnight and the loaded time is some time after midnight?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
In the Access data source file, the Gtime and Ttime fields are text.
So this works:
abs((left(gtime,2)*60+righ t(gtime,2) )-(left(tt ime,2)*60+ right(ttim e,2)))
and so does this
abs(1440*(timevalue(gtime) -timevalue (ttime)))
So this works:
abs((left(gtime,2)*60+righ
and so does this
abs(1440*(timevalue(gtime)
The cleanest solution would then probably be
abs(datediff("n", timevalue(GTime), timevalue(TTime)))
abs(datediff("n", timevalue(GTime), timevalue(TTime)))
ASKER
Cdate(timevalue(gtime)-tim evalue(tti me)) gives:
1899-12-30 00:29:00
1899-12-30 00:37:00
1899-12-30 00:33:00
1899-12-30 00:34:00
1899-12-30 00:44:00
abs(datediff("n", GTime, TTime)) gives:
"Too few parameters, Expected 1."
1899-12-30 00:29:00
1899-12-30 00:37:00
1899-12-30 00:33:00
1899-12-30 00:34:00
1899-12-30 00:44:00
abs(datediff("n", GTime, TTime)) gives:
"Too few parameters, Expected 1."
ASKER
abs(datediff("n", timevalue(GTime), timevalue(TTime)))
also gives
"Too few parameters, Expected 1."
also gives
"Too few parameters, Expected 1."
Must be abs(datediff(minute, GTime, TTime))
or abs(datediff(minute, timevalue(GTime), timevalue(TTime)))
or abs(datediff(minute, timevalue(GTime), timevalue(TTime)))
I'm running A2003 and do not get the 1899 date. However you can get rid of it by wrapping with timeValue()
tb = time()
ta = time()+.1
? ta
14:54:12
? tb
12:29:28
?ta-tb
0.100196759259259
? timevalue(cdate(ta-tb))
02:24:44
tb = time()
ta = time()+.1
? ta
14:54:12
? tb
12:29:28
?ta-tb
0.100196759259259
? timevalue(cdate(ta-tb))
02:24:44
ASKER
I appreciate the assistance. Thanks very much.
ASKER
Tommy - these
Must be abs(datediff(minute, GTime, TTime))
or abs(datediff(minute, timevalue(GTime), timevalue(TTime)))
both still yield
"Too few parameters. Expected 1."
Must be abs(datediff(minute, GTime, TTime))
or abs(datediff(minute, timevalue(GTime), timevalue(TTime)))
both still yield
"Too few parameters. Expected 1."
Thanks, but why the B? I explained away the 1899-2-30.
The query should support datediff and n means minutes so abs(datediff("n", GTime, TTime)) should get you what you want. More datediff stuff here http://www.techonthenet.com/access/functions/date/datediff.php
If that doesn't do it for you, read on.
If your times are stored as strings, then there really isn't a much better way to do it than you have. If they are stored as numbers then they are stored in days so abs(GTime - TTime)*24*60 will give you the minutes.