Computing time difference

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi, I am a novice to access and facing a problem while updated a text field.
I have three text boxes on a form; start and end times of an event and
another text box which shows the time difference in minutes. I am trying to
compute the time difference in minutes automatically once the end time has
been updated / changed.
I did the following and it dosen't seem to work:
Set the After update even of txEndTime ( using expression builder as well as
code builder)
[1] expression builder: [txTimeTaken] =
DateDiff("n",[txStartTime],[txEndTime])
~ This dosen't seem to do anything when I change the value in txEndTime
[2] code builder:
Private Sub txtEndTime_AfterUpdate()
Set txTimeTaken.Value = DateDiff("n", txStartTime, txEndtime)
End Sub
~ This sets the value to '0'.

Any inputs would be much appreciated.

TIA
cole
 
The problem is probably that the values from your text boxes are not actually
date data types. The DateDiff function only works correctly with dates.
Because dates are actually stored as numbers, it performs the calculation and
comes out to 0 because hours, minutes, and seconds are carried as fractions.
For example, for today (4/29/2005), the Date() returns 38471. The Now()
returns 4/29/2005 5:04:27 PM as 38471.7114236111.

With all that theory behind us, it is relatvive to know how you are entering
start and end times. Like 2:45 PM? It will make a difference in how you
calculate. It is necessary, in this instance, to look at the value in the
text box, take out any extranuous characters, multiply the hours by 60 and
add the minutes.
If you need additional help with this, post back with specifics on how the
data is being entered.
 
Thanks for your reponse. I am entring the start and end times in "Short Time"
format. Ex: (Say) Start Time = 11:30, End Time = 14:00.



Klatuu said:
The problem is probably that the values from your text boxes are not actually
date data types. The DateDiff function only works correctly with dates.
Because dates are actually stored as numbers, it performs the calculation and
comes out to 0 because hours, minutes, and seconds are carried as fractions.
For example, for today (4/29/2005), the Date() returns 38471. The Now()
returns 4/29/2005 5:04:27 PM as 38471.7114236111.

With all that theory behind us, it is relatvive to know how you are entering
start and end times. Like 2:45 PM? It will make a difference in how you
calculate. It is necessary, in this instance, to look at the value in the
text box, take out any extranuous characters, multiply the hours by 60 and
add the minutes.
If you need additional help with this, post back with specifics on how the
data is being entered.

J Cole said:
Hi, I am a novice to access and facing a problem while updated a text field.
I have three text boxes on a form; start and end times of an event and
another text box which shows the time difference in minutes. I am trying to
compute the time difference in minutes automatically once the end time has
been updated / changed.
I did the following and it dosen't seem to work:
Set the After update even of txEndTime ( using expression builder as well as
code builder)
[1] expression builder: [txTimeTaken] =
DateDiff("n",[txStartTime],[txEndTime])
~ This dosen't seem to do anything when I change the value in txEndTime
[2] code builder:
Private Sub txtEndTime_AfterUpdate()
Set txTimeTaken.Value = DateDiff("n", txStartTime, txEndtime)
End Sub
~ This sets the value to '0'.

Any inputs would be much appreciated.

TIA
cole
Click to expand...
Click to expand...
 
Cole

Try this, I have two unbound text boxes called actual_start and
actual_finish the time difference is a text box called time_taken

All text boxes have the format property set to short time with the input
masking as 00:00;0;_

I have created an after update event for actual_finish as follows

Dim temp_time As Variant

If Me!actual_Start > Me!actual_finish Then
temp_time = 1 + Me!actual_finish - Me!actual_Start
Else
temp_time = actual_Start - Me!actual_finish
End If

Me!time_taken = temp_time

the line temp_time = 1 + Me!actual_finish - Me!actual_Start is when the
finish time is the next day. This works OK for a 24 hr period e.g. start
06:00 finish 05:59 the next day gives 23:59

If want time for more than obne day you would have to do a date diff on the
date then multiply by 1440 to convert ot minutes.

Allan Murphy
Email: (e-mail address removed)
 
Cole,

Are these textboxes bound or unbound? If they are bound, are the table
fields that they are bound to defined as a Date/Time data type. The
format of the entry in the textboxes is not relevant, but as Klatuu
pointed out, they need to be defined as date/time for the DateDiff
function to work. Otherwise, I would expect the DateDiff function to
return the correct time interval in minutes. However, the use of the
word Set in your code is not correct. Should be like this...
Me.txTimeTaken = DateDiff("n", Me.txStartTime, Me.txEndtime)
 
Back
Top