Solved

Access Date and Time Math Functions

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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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.

809 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