Solved

Date and Time Questions in Access 2013

Posted on 2015-01-06
5
258 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 119

Assisted Solution

by:Rey Obrero
Rey Obrero 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 34

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 34

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Familiarize people with the process of utilizing SQL Server views 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 Microsoft Access…
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.

746 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now