Calculations with times

[Nick T]

Hi,

Got a form with 2 text boxes on it.
Each text box has a date/time in it such as:

09/03/2009 10:30:25 and 09/03/2009 11:30:25

In a 3rd text box on my form, can i add any code anywhere which based on the
info in text box 1 and 2, calculates the time difference between the two, ie,
for the example above the difference would be 1hr. therefore text box 3 would
say 01:00:00 (or somthing simular).

Any suggestions anyone??

Thanks

 

[Jerry Whittle]

Format(CDate([TextBox2] - [TextBox1]), "h:nn:ss")

The above will work for less than 24 hours. Once above a day, you'll need
something else as it will only show up to 24 hours then start back over
again. In other words, a difference of 27 hours will only display 3 hours.

 

[Nick T]

Hi thanks for that
Any suggestions if i do want to do more than 24hrs?

Thanks

Format(CDate([TextBox2] - [TextBox1]), "h:nn:ss")

The above will work for less than 24 hours. Once above a day, you'll need
something else as it will only show up to 24 hours then start back over
again. In other words, a difference of 27 hours will only display 3 hours.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Hi,

Got a form with 2 text boxes on it.
Each text box has a date/time in it such as:

09/03/2009 10:30:25 and 09/03/2009 11:30:25

In a 3rd text box on my form, can i add any code anywhere which based on the
info in text box 1 and 2, calculates the time difference between the two, ie,
for the example above the difference would be 1hr. therefore text box 3 would
say 01:00:00 (or somthing simular).

Any suggestions anyone??

Thanks

 

[Jerry Whittle]

I just knew that you were going to ask!

Changing the format to the following will give you up to 31 days:
"d h:nn:ss"

The following will give you the number of days and the decimal part of the
day:
CDbl([TextBox2] - [TextBox1])
could look something like 2.04166666666424
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Hi thanks for that
Any suggestions if i do want to do more than 24hrs?

Thanks

Format(CDate([TextBox2] - [TextBox1]), "h:nn:ss")

The above will work for less than 24 hours. Once above a day, you'll need
something else as it will only show up to 24 hours then start back over
again. In other words, a difference of 27 hours will only display 3 hours.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Hi,

Got a form with 2 text boxes on it.
Each text box has a date/time in it such as:

09/03/2009 10:30:25 and 09/03/2009 11:30:25

In a 3rd text box on my form, can i add any code anywhere which based on the
info in text box 1 and 2, calculates the time difference between the two, ie,
for the example above the difference would be 1hr. therefore text box 3 would
say 01:00:00 (or somthing simular).

Any suggestions anyone??

Thanks

 

[James A. Fortune]

Hi,

Got a form with 2 text boxes on it.
Each text box has a date/time in it such as:

09/03/2009 10:30:25 and 09/03/2009 11:30:25

In a 3rd text box on my form, can i add any code anywhere which based on the
info in text box 1 and 2, calculates the time difference between the two, ie,
for the example above the difference would be 1hr. therefore text box 3 would
say 01:00:00 (or somthing simular).

Any suggestions anyone??

Thanks

Try:

http://groups.google.com/group/microsoft.public.access/browse_frm/thread/be7e101c5176ebd8

James A. Fortune
(e-mail address removed)

 

[Nick T]

Excellent, thanks for that!

I just knew that you were going to ask!

Changing the format to the following will give you up to 31 days:
"d h:nn:ss"

The following will give you the number of days and the decimal part of the
day:
CDbl([TextBox2] - [TextBox1])
could look something like 2.04166666666424
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Hi thanks for that
Any suggestions if i do want to do more than 24hrs?

Thanks

Format(CDate([TextBox2] - [TextBox1]), "h:nn:ss")

The above will work for less than 24 hours. Once above a day, you'll need
something else as it will only show up to 24 hours then start back over
again. In other words, a difference of 27 hours will only display 3 hours.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

:

Hi,

Got a form with 2 text boxes on it.
Each text box has a date/time in it such as:

09/03/2009 10:30:25 and 09/03/2009 11:30:25

In a 3rd text box on my form, can i add any code anywhere which based on the
info in text box 1 and 2, calculates the time difference between the two, ie,
for the example above the difference would be 1hr. therefore text box 3 would
say 01:00:00 (or somthing simular).

Any suggestions anyone??

Thanks

 

[Bob Quintal]

Hi,

Got a form with 2 text boxes on it.
Each text box has a date/time in it such as:

09/03/2009 10:30:25 and 09/03/2009 11:30:25

In a 3rd text box on my form, can i add any code anywhere which
based on the info in text box 1 and 2, calculates the time
difference between the two, ie, for the example above the
difference would be 1hr. therefore text box 3 would say 01:00:00
(or somthing simular).

Any suggestions anyone??

Thanks

= Datediff("s",[startTime],[endTime]) will give the number of seconds
between the fields StartTime and endTime

To format it as hours and minutes and seconds, you need a custom
format routine. Put this in a module and call wherever needed.

public function Sec2Dur(duration as double) as string
dim hours as integer, mins as integer, secs as integer
hours = int(duration * 3600)
mins = int(duration*60) - hours*60
secs = duration - (hours*3600-mins*60)
sec2dur = format(hours,"000") _
& ":" & format(mins,"00") _
& ":" & format(secs,"00")
end function

With that, put the following in your third textbox's controlsource
=Sec2Dur(Datediff("s",[startTime],[endTime]))

change the names startTime and EndTime to the names of your two
textboxes