Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 400
  • Last Modified:

ACCESS DATA SOURCE DATE TIME

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
BOEING39
Asked:
BOEING39
  • 4
  • 4
1 Solution
 
Patrick MatthewsCommented:
BOEING39,

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

Patrick
0
 
BOEING39Author Commented:
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
 
Patrick MatthewsCommented:
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
BOEING39Author Commented:
Use his in access SQL statement in New column?    or new query?
0
 
BOEING39Author Commented:
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
 
Patrick MatthewsCommented:
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
 
BOEING39Author Commented:
Accurate quick responses.  Thank you.
0
 
Patrick MatthewsCommented:
Glad to help :)
0

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now