Solved

MS Access Date Field / Time Difference

Posted on 2009-05-08
6
481 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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 
LVL 16

Accepted Solution

by:
Chuck Wood earned 250 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 250 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
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…
Familiarize people with the process of utilizing SQL Server stored procedures 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 Micr…
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…

751 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