Solved

ACCESS DATA SOURCE DATE TIME

Posted on 2011-09-24
8
385 Views
Last Modified: 2012-05-12
I have an access data base   (MDB) file where i want to perform a column hour/min count (in "hours" & "Mins) format.
The column criteria I have is  the "date" and a column for the associated "time".   What would the formula or SQL Query look like to produce this in the following format based on the date & time entered.

example:     1 day and a 1/2 day would produce the following format:     36:00 (hrs/mins)
0
Comment
Question by:BOEING39
[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
  • 4
  • 4
8 Comments
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 36593663
BOEING39,

It's not clwar what you're trying to do.  Can you provide a more substantial example?

Patrick
0
 

Author Comment

by:BOEING39
ID: 36593802
I have attached the DB.    Please look at "AOSQuery" specifically columns  "ETRDate" & "ETRTime".    I need to generate a new column in this Query that will display the elapsed time in (hrs:mins format) from when these dates are entered. into the DB.    As I mentioned a day and 1/2 would look like   36:00    or 36 hours and 00 minutes.    
database2.mdb
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 36594006
Try this:

SELECT ID, Dates, Ship, ArrTime, InFlt, History, ETRDate, ETRTime, ETR, Station, Status1, 
    Updated, Status1, DateDiff("h", Dates + ArrTime, ETRDate + ETRTime) & ":" & 
    Format(DateDiff("n", Dates + ArrTime, ETRDate + ETRTime) Mod 60, "00") AS Elapsed
FROM AOS;

Open in new window

0
Independent Software Vendors: 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!

 

Author Comment

by:BOEING39
ID: 36594084
Use his in access SQL statement in New column?    or new query?
0
 

Author Comment

by:BOEING39
ID: 36594091
Placed it in as a SQL Query the format looks good.    Could you provide SQL for arrival "date" and "time",  with "Now() function?  Instead of the "ETRDate" & "ETRTime" comparison?
0
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 500 total points
ID: 36594295
SELECT ID, Dates, Ship, ArrTime, InFlt, History, ETRDate, ETRTime, ETR, Station, 
    Status1, Updated, Status1, DateDiff("h", Dates + ArrTime, Now()) & ":" & 
    Format(DateDiff("n", Dates + ArrTime, Now()) Mod 60, "00") AS Elapsed
FROM AOS;

Open in new window

0
 

Author Closing Comment

by:BOEING39
ID: 36595105
Accurate quick responses.  Thank you.
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 36595595
Glad to help :)
0

Featured Post

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

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

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

726 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