Solved

Access Date and Time Math Functions

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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

688 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