Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Days Elapsed and Hours Elapsed in Access 2013

Posted on 2015-01-21
3
Medium Priority
?
196 Views
Last Modified: 2015-01-26
The Facts:

I have some Tables that I am not allowed to touch as far changing data types or data in it.  I need to create a query that another data base is going to be pulling from.

I have two columns that have the DataType set as Numbers using the m/dd/yyy h:nn format (not date/time)

I need to take the difference of these columns and show as two new columns
DaysElapsed (needs to be in decimals - like 1.23 days)
HoursElapsed (needs to show more than 24 hours, so if it took an exact day and one half, the number would 36

My end goal is that I am working in another system that needs these showing in the above format.
0
Comment
Question by:rnhturner1
3 Comments
 
LVL 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 2000 total points
ID: 40562245
If the "Numbers" are legitimate Date values, then you can use CDate to convert them to a Date value, then use standard Date functions to determine the differences.

So you could do something like this:

DaysElapsed: DateDiff("h", CDate(Col1), CDate(Col2)) / 24

Note you must use the "forward slash" ( / ) and not the "backward slash ( \ ) to get the right result.

HoursElapsed: DateDiff("h", cDate(Col1), CDate(Col2))

Here's the MSDN entry on DateDiff:

https://support.office.com/en-US/Article/DateDiff-Function-e6dd7ee6-3d01-4531-905c-e24fc238f85f?ui=en-US&rs=en-001&ad=US
0
 
LVL 40

Expert Comment

by:PatHartman
ID: 40562271
the Date/time data type is actually a double precision number with the integerportion representing the number of days since 12/30/1899 and the decimal being the time since midnight.  Negative numbers are for dates prior to 12/30/1899.  So for today:
print now()
1/21/2015 11:45:49 AM
print cdbl(now())
 42025.4902430556

So, you say the field is a number but it is formatted as a date.  Does that mean it works like a real date/time field?  We need to know the meaning of the parts in order to know how to do the calculation.

Can you provide some data samples showing both the native value and the formatted value?
0
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 40562808
Try this:

DaysElapsed: DateEnd-DateStart
HoursElapsed:  DateEnd-DateStart * 24

/gustav
0

Featured Post

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.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
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.

886 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