Solved

Access Date and Time Math Functions

Posted on 2009-05-04
4
1,305 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Before you start you should really change them to one field of type Date/Time
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
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.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

763 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now