Solved

Date and Time Questions in Access 2013

Posted on 2015-01-06
5
276 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 37

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 37

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

734 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