Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

ACCESS DATA SOURCE DATE TIME

Posted on 2011-09-24
8
Medium Priority
?
398 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 93

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 93

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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

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 93

Accepted Solution

by:
Patrick Matthews earned 2000 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 93

Expert Comment

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

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
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.
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…

618 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