?
Solved

Access Date and Time Math Functions

Posted on 2009-05-04
4
Medium Priority
?
1,312 Views
Last Modified: 2013-11-27
Experts

I am having some difficulty getting the results that I am looking for out of my access query (SQL below).  I have a date field and a time field that show when a trouble ticket was opened, what I want to do is some how figure out how long in DECIMAL HOURS (hour and a half = 1.5 for example) a trouble ticket has been open at the time the report is run.  I was trying to add the date + the time field and subtract this from the NOW() function but am not having any luck.

VBA or SQL either one will work for me, I just need to get the right output, this is the last query that is run prior to the exportation of the results to excel via an automation macro, currently this all happens without any manual input and I want to keep it that way, I just need to add one last field.

Any help would be much appreciated.

Thanks
SELECT DISTINCT TOP 25 tbl_Top_25_main.[TKT#], tbl_Top_25_main.[DATE REC], tbl_Top_25_main.[TIME REC], tbl_Top_25_main.[REPORT CAT], tbl_Top_25_main.[RESPONSIBLE MCO], tbl_Top_25_main.[CURRENT STATUS]
FROM tbl_Top_25_main
WHERE tbl_Top_25_main.[CURRENT STATUS] = (SELECT MAX(t2.[CURRENT STATUS]) FROM tbl_Top_25_main t2 WHERE tbl_Top_25_main.[TKT#] = t2.[TKT#])
GROUP BY tbl_Top_25_main.[TKT#], tbl_Top_25_main.[DATE REC], tbl_Top_25_main.[TIME REC], tbl_Top_25_main.[REPORT CAT], tbl_Top_25_main.[RESPONSIBLE MCO], tbl_Top_25_main.[CURRENT STATUS]
HAVING (((tbl_Top_25_main.[CURRENT STATUS]) Is Not Null))
ORDER BY tbl_Top_25_main.[DATE REC], tbl_Top_25_main.[TIME REC];

Open in new window

0
Comment
Question by:jclem1
[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
  • 2
4 Comments
 
LVL 46

Expert Comment

by:tbsgadi
ID: 24301886
Hi jclem1,

You have 2 fields, .[DATE REC] &.[TIME REC], they should really be one field that has datatype Date/Time.
Can you tell us what Datatype the .[TIME REC] has & give examples of the values.



Gary
0
 
LVL 77

Accepted Solution

by:
peter57r earned 2000 total points
ID: 24302054
SELECT DISTINCT TOP 25 tbl_Top_25_main.[TKT#], tbl_Top_25_main.[DATE REC], tbl_Top_25_main.[TIME REC], tbl_Top_25_main.[REPORT CAT], tbl_Top_25_main.[RESPONSIBLE MCO], tbl_Top_25_main.[CURRENT STATUS],
(Now()-([Date rec] +[Time rec]))*24 as Open
FROM tbl_Top_25_main
WHERE tbl_Top_25_main.[CURRENT STATUS] = (SELECT MAX(t2.[CURRENT STATUS]) FROM tbl_Top_25_main t2 WHERE tbl_Top_25_main.[TKT#] = t2.[TKT#])
ORDER BY tbl_Top_25_main.[DATE REC], tbl_Top_25_main.[TIME REC];
 

0
 
LVL 1

Author Comment

by:jclem1
ID: 24303635
tbsqadi

Date and Time are both text values at the moment but I can change them to whatever I need to.  

DATE REC      TIME REC
01/17/09      11:44
0
 
LVL 46

Expert Comment

by:tbsgadi
ID: 24303742
Before you start you should really change them to one field of type Date/Time
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
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.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses

770 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