By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,215 Members | 1,517 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,215 IT Pros & Developers. It's quick & easy.

Time Calculation in form control

P: 171
Hi All

I have searched Bytes for an answer to my question so many hours.
I couldnt find threads related to my problem.
I have a form with 2 controls, both as short time formats. I need to get the sum of the time on a differnt control.
tme1 as 23:00
tme2 as 00:11
in control tme3 i need the answer 23:11
This should happen after update of tme2.

Hope my question is clear. Please help me solve this issue

Jul 12 '09 #1
Share this Question
Share on Google+
3 Replies

Expert 2.5K+
P: 3,532
First off, you need to understand that these are not "time" fields, which in Access VBA are actually called Date/Time fields! You cannot add two true times together any more than you can add Monday + Tuesday together or 7/12/2009 + 7/13/2009.

"Time" fields must, by definition, also include a date, even if it's not displayed. If you only enter the "time" portion of a Date/Time field, Access will automatically tack on the current date. Your fields are actually storing hours and minutes, formatted so that it looks like a "time."

Because you cannot be sure of the order that your user enters the data, you need to do this calculation in the AfterUpdate event of both tme1 and tme2.

The first question we need to address is how are tme1 and tme2 defined in the underlying table? Because of they way you're displaying them, they need to be defined as Text fields.

The next question is what do you want to do if one of these fields does not contain data? Enter the value of the single populated field in tme3, enter a zero or leave the field Null (blank.)

The following code does what you want if both fields are populated with data. If one or the other aren't populated, it sets tme3 to Null.
Expand|Select|Wrap|Line Numbers
  1. Private Sub tme2_AfterUpdate()
  2.  Dim Minutes1 As Integer
  3.  Dim Minutes2 As Integer
  4.  Dim Minutes3 As Integer
  6.  If Not IsNull(Me.tme1) And Not IsNull(Me.tme2) Then
  7.    Minutes1 = (Val(Left(Me.tme1, InStr(Me.tme1, ":"))) * 60) + Val(Mid(Me.tme1, InStr(Me.tme1, ":") + 1))
  8.    Minutes2 = (Val(Left(Me.tme2, InStr(Me.tme2, ":"))) * 60) + Val(Mid(Me.tme2, InStr(Me.tme2, ":") + 1))
  9.    Minutes3 = Minutes1 + Minutes2
  10.    Me.tme3 = (Minutes3 \ 60) & ":" & Minutes3 Mod 60
  11.  Else
  12.    Me.tme3 = Null
  13.  End If
  14. End Sub
I put it in the tme2 AfterUpdate event, as you asked, but as I said above, it should also be in the AfterUpdate for tme1, as well.

If you are not going to store tme3 in the underlying table (and you really shouldn't store this kind of calculated field) you will also need to place the same code in the Form_Current event so that it is re-calculated when you move from record to record.

Linq ;0)>
Jul 12 '09 #2

P: 171
Thank you for the reply. I think i didnt explain properly. My situation is;
I have a form with startdate (current date) and three time controls and an enddate.User enters starttime and an elapse time. on the third time control i get the times added . Now i am getting the time added on the third time control simply using control source as time1+time2.
what i want to do is- if the time3 is after midnight, i need the end date updated as the nextdate from startdate. Startdate and end date is medium date format and time control are short time format. Because after the entry made i want update that to a table with all these values.
By the way same database i am facing another problem is in that form is it possible to call the table which i am updating to view the details as entered at the same time in a subform?


THank you for the patience and prompt support from you all
Jul 13 '09 #3

Expert 100+
P: 344
Not sure what you are using this for, but, if you populate the 1st field with NOW(), i.e. current date and time, and then when the form is completed, populate the third field with NOW(), new current date and time, you can then use the datedifference function to find the elasped times. There would be no need to a user to enter time at all.

Also remember, if you are working on time critical operations, the PC clock is not the most reliable in the world. Mine for example says 11:29 right now, the radio says 10:53. Unless your pc are updated by a time server, they wont give accurate times.
Jul 13 '09 #4

Post your reply

Sign in to post your reply or Sign up for a free account.