Solved

Access Date and Time Math Functions

Posted on 2009-05-04
4
1,310 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 500 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

739 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