[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 401
  • 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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
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

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

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