?
Solved

Microsoft Query Functions

Posted on 2010-11-07
12
Medium Priority
?
472 Views
Last Modified: 2012-05-10
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(TTime,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.)

0
Comment
Question by:jkasavan
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 3
12 Comments
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 34081891
MS Query is based mostly on SQL and there are so many books out on that that I certainly couldn't write one worth anything.

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.
0
 
LVL 44

Expert Comment

by:GRayL
ID: 34084950
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?
0
 
LVL 44

Accepted Solution

by:
GRayL earned 375 total points
ID: 34084994
If you had two variable TBefore and TAfter as the two times, both datetime datatypes, then you can get the difference by:

CDate(TAfter-TBefore)

which shows as hh:nn:ss
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 2

Author Comment

by:jkasavan
ID: 34089411
In the Access data source file, the Gtime and Ttime fields are text.

So this works:

abs((left(gtime,2)*60+right(gtime,2))-(left(ttime,2)*60+right(ttime,2)))

and so does this

abs(1440*(timevalue(gtime)-timevalue(ttime)))
0
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 34089430
The cleanest solution would then probably be
abs(datediff("n", timevalue(GTime), timevalue(TTime)))
0
 
LVL 2

Author Comment

by:jkasavan
ID: 34089494
Cdate(timevalue(gtime)-timevalue(ttime)) 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."

0
 
LVL 2

Author Comment

by:jkasavan
ID: 34089498
abs(datediff("n", timevalue(GTime), timevalue(TTime)))

also gives

"Too few parameters, Expected 1."
0
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 34089644
Must be abs(datediff(minute, GTime, TTime))
or abs(datediff(minute, timevalue(GTime), timevalue(TTime)))
0
 
LVL 44

Expert Comment

by:GRayL
ID: 34095179
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
0
 
LVL 2

Author Closing Comment

by:jkasavan
ID: 34098392
I appreciate the assistance. Thanks very much.
0
 
LVL 2

Author Comment

by:jkasavan
ID: 34098394
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."
0
 
LVL 44

Expert Comment

by:GRayL
ID: 34098736
Thanks, but why the B?  I explained away the 1899-2-30.
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

801 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question