Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

MS Access Date Field / Time Difference

Posted on 2009-05-08
6
Medium Priority
?
493 Views
Last Modified: 2013-11-27
Here is what im using to populate the [time]  field with the time differenceon my form

[Forms]![RCAFRM]![time] = [Forms]![RCAFRM]![SERVAVA] - [Forms]![RCAFRM]![SERVUNA]

My problem is I want to store the time difference in hours / minutes but when I save the record it saves in in time format.

Example = 0:41 will savs as 12:41:00 AM

The format on the time feild is Short Time
0
Comment
Question by:mpden32
[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
6 Comments
 
LVL 21

Expert Comment

by:oleggold
ID: 24337414
You need a variable to calculate Your time first ,possible in int conversion,then populate like,try this
dim dif as variant
dif =  [Forms]![RCAFRM]![SERVAVA] - [Forms]![RCAFRM]![SERVUNA]
[Forms]![RCAFRM]![time] =dif
0
 
LVL 16

Expert Comment

by:Chuck Wood
ID: 24337425
A time field will only save time (7:30 AM). You need to use a Text field and format the hours:minutes text string to put in it.
0
 

Author Comment

by:mpden32
ID: 24337696
Could you please help me with and example of how to configure my textbox on the from as well as the configureation of the field typr on my table
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
LVL 16

Accepted Solution

by:
Chuck Wood earned 1000 total points
ID: 24337786
The line in the snippet should convert the 0:41 to a string. The field where you save this value (typr?) should have its DataType changed to Text.
[Forms]![RCAFRM]![time] = CStr([Forms]![RCAFRM]![SERVAVA] - [Forms]![RCAFRM]![SERVUNA])

Open in new window

0
 
LVL 27

Assisted Solution

by:MikeToole
MikeToole earned 1000 total points
ID: 24338075
Datetime values are stored as floating point numbers in Access, e.g. 6pm today is 39939.75
- the .75 represents the time as three quarters of a day, and 39939 is the number of days since 31 Dec 1899
Any formatting you see will be as a result of formatting applied in the user interface.
Presumably the time difference in question really is 41 mins. This would be stored as 0.0284722222222222         i.e.      41/(24*60)
If you format the textbox as Medium Time this will display as 12:41 AM      -    i.e 41 minutes after midnight
Change the format to Short Time and it will display as 00:41 - just as you want.
This will handle time differences upto 23:59  

0
 

Author Comment

by:mpden32
ID: 24338114
Ok my goal is to get total time for events if I convert and save the time in my table \ field=totaltime how can I add those together and display back in hous and minutes
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

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…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

661 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