Solved

ACCESS DATA SOURCE DATE TIME

Posted on 2011-09-24
8
346 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
  • 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
 

Author Comment

by:BOEING39
ID: 36594084
Use his in access SQL statement in New column?    or new query?
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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.

708 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

20 Experts available now in Live!

Get 1:1 Help Now