Solved

MS Access Date Field / Time Difference

Posted on 2009-05-08
6
454 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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

929 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

12 Experts available now in Live!

Get 1:1 Help Now