?
Solved

Date and Time Questions in Access 2013

Posted on 2015-01-06
5
Medium Priority
?
293 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 668 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 664 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 39

Accepted Solution

by:
PatHartman earned 668 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 39

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

Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
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: …

719 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