Solved

Date and Time Questions in Access 2013

Posted on 2015-01-06
5
272 Views
Last Modified: 2015-01-07
USING ACCESS 2013

Two of the fields that I am trying to use are
Created Time - which will look something like - 1/10/2013 2:04:00 PM
Closed Time - which will look something like  - 2/28/2013 8:17:00 AM

I want to create another column that will calculate the total time this ticket was open.  
HH:MM or Days,Hours, Minutes may work good too.

The problem is that when I try to do this, it only shows up to 24 hours.  How can get it to show properly.
0
Comment
Question by:rnhturner1
5 Comments
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 167 total points
ID: 40533935
you will need a User define function in VBA to do this

see this GetElapsedTime()
0
 
LVL 26

Assisted Solution

by:Nick67
Nick67 earned 166 total points
ID: 40533967
Understanding DateTime in Access is very important.
DateTimes are stored as a special kind of double.
Zero day is 30-Dec-1899
Today is 41000+ days later
Times are stored as the positive fraction of the day
0.3333333333333333 is 8 am
0.5 is noon
0.6666666666666666 is 6 pm
This is what is stored
What is displayed is whatever format you apply to a datetime field or variable

Right now it is 6-Jan-2015 10:55 AM
Format(#6-Jan-2015 10:55 AM#, "dddd") will be Tuesday
Format(#6-Jan-2015 10:55 AM#, "hh:mm AMPM") 10:55 AM
Format(#6-Jan-2015 10:55 AM#, "yyyy-mm-dd") 2015-01-06

When you do a calculation with datetime, in the background Access is working with the Double values
So subtracting yesterday at 8 am from today at 8 pm will yield 1.33333333333333
Now -- what to do with it!
Displaying it as any kind of a date format won't be pretty - 1.3333333333333333 is like 31-Dec-1899 8:00:00 AM
But you can break it down and display it as a string
Coerce it to integer and you get the days: numDays = CInt(1.33333333333333333)
Subtract the days from it and multiply by 24 and you get hours
numHours = (1.3333333333333333 - CInt(1.333333333333333)) *24
Minutes is the same idea with 60
Put them together
strTimeOpen = numDays & " " & numHours & ":" & numMinutes

or go straight hours (24 * 1.33333333333333)

I want to create another column that will calculate the total time this ticket was open.
Generally, in tables anyway, what's the point of storing a calculated field?
It's information is contained in the fields it is computed from -- and what if you change those?
0
 
LVL 36

Accepted Solution

by:
PatHartman earned 167 total points
ID: 40533987
It isn't necessary to save the calculated difference and in fact, saving it would be considered poor practice.  It is best to calculate the difference either in a query or in a report.  I'm attaching a sample database of date functions.  One of them returns the difference between two dates using the spec you provide.
UsefulDateFunctions121008b.zip
0
 
LVL 36

Expert Comment

by:PatHartman
ID: 40534105
I realize that some people think that all participants should get a trophy but surely you didn't use all three methods.  There are occasions where it makes sense to split the award because it took the contributions of multiple people to get to the solution but here we have three completely different options.  I would have expected you to choose one option to award all the points and thank the others.  I assure you that even though I think my solution is the best given that it provides tested code that you can simply copy into your app and call, I would not have been offended if you had awarded the points to the solution you actually used.
0
 

Author Comment

by:rnhturner1
ID: 40536070
I am new to this site.  And I am a newby to ACCESS.  I don't even know how to use the code you gave me.  I am left to still figure it out on my own, since I needed more detail.  I do appreciate your help and more than likely it is the best solution, but I don't know yet.
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
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…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

730 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