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

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

Date and Time Questions in Access 2013

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
rnhturner1
Asked:
rnhturner1
3 Solutions
 
Rey Obrero (Capricorn1)Commented:
you will need a User define function in VBA to do this

see this GetElapsedTime()
0
 
Nick67Commented:
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
 
PatHartmanCommented:
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
 
PatHartmanCommented:
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
 
rnhturner1Author Commented:
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

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

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