Calculate Value 3

[BT24]

Hello

What i need to know is how do i get a calculation to work when i already have a control source for a text box... What i have is a Start time box and a Finish time box but what i want is afterupdate of the finish time box that the duration text box gets a total of hours worked desplayed into it. thanks for whatever help you can give, it is truly appraicated

BT24

 

[GDGarth]

Try using the DateDiff function (the syntax is below):

DateDiff(interval, date1, date2[, firstdayofweek[, firstweekofyear]])

In your case, you should be able to use:

DateDiff("h",[Start Time],[Finish Time])

Start Time and Finish Time are the values in your text boxes. This will show the number of HOURS - you may want it to show minutes (substitute "n" for "h" in the above formula), so 5 hours and 50 minutes doesn't show up as "5".

You can easily convert 350 minutes to hours and minutes.

 

[BT24]

Thanks
But how will i write it so that it will populate the Duration text box? will i write it as

Private sub Afterupdate_Duration
DateDiff("h",[Start Time],[Finish Time])
end sub

is that what should be in the expression?

thanks again

BT24

 

[BT24]

Thanks
But how will i write it so that it will populate the Duration text box? will i write it as

Private sub Afterupdate_Duration()
DateDiff("h",[Start Time],[Finish Time])
end sub

is that what should be in the expression?

thanks again

BT24

 

[BT24]

Thanks
But how will i write it so that it will populate the Duration text box? will i write it as

Private sub Afterupdate_Duration()
DateDiff("n",[Start Time],[Finish Time])
end sub

is that what should be in the expression?

thanks again

BT24

 

[GDGarth]

Try this:

Private sub Afterupdate_Finish()

Dim ElapsedTime
ElapsedTime=DateDiff("h",[Start Time],[Finish Time])
[Duration]=ElapsedTime

end sub

Since you're entering the start and finish times, you need to put the event in one of those boxes, not the duration box. Also, remember that this will only give you the number of hours - if you want to know hours and minutes, you'll need to get total minutes, and convert (divide by 60).

You may want to add some error handling, etc. to the code.

 

[BT24]

Wow!!

that worked perfectly! thanks alot for the help, sorry about the 3 extra submissions.

BT24

 

[sdk]

Hi,

Although you are obviously happy with your solution, an alternative would've been to simply have the controlSource of the duration field set to

=DateDiff("h",[Start Time],[Finish Time])

This would still update as required and been a little simpler.

Cheers

Steve

 

[GDGarth]

Good idea - I wish I'd thought of that!

 

[MichaelRed]

And ... if the "duration' will never exceed 24 Hours:

=Format([Finish Time] - [Start Time], "hh:mm&quot

And there is no conversion (to Hours and Minutes) necessary
MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[BT24]

Thank you MichaelRed that also works great

BT24