Solved

MS Access Date Field / Time Difference

Posted on 2009-05-08
6
442 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
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
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.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

757 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

20 Experts available now in Live!

Get 1:1 Help Now