VBA Function?

[christian]

Hi all,

I've four fields in a form that I want to add, I want them to be equal
to total hours; but I don't know how.The data types of these fields is
TEXT.

SET UET Othertime Total Hours
05:00 05:00 5:00 15:00

[Wayne Morgan]

Re: VBA Function?

Try the following in the Contol Source of the Total Hours textbox. It
should, of course, all be on one line.

=Format(((CInt( Left([SET], 2))+CInt(Left([UET], 2))+CInt(Left([OtherTime],
2)))*60+(CInt(R ight([SET], 2))+CInt(Right([UET], 2))+CInt(Right([OtherTime],
2))))\60, "00") & ":" & Format(((CInt(L eft([SET], 2))+CInt(Left([UET],
2))+CInt(Left([OtherTime], 2)))*60+(CInt(R ight([SET], 2))+CInt(Right([UET],
2))+CInt(Right([OtherTime], 2)))) Mod 60, "00")

This assumes 2 characters to the Left and Right of the colon. If there may
be only one number to the left of the colon, as in your Othertime example,
use Val instead of CInt on the Left items. Val will change ("5:") to 5 where
as CInt will return an error.
--
Wayne Morgan
MS Access MVP


"christian" <csepulveda@par tners.org> wrote in message
news:e1cc3cc3.0 404230622.3f911 f12@posting.goo gle.com...[color=blue]
> Hi all,
>
> I've four fields in a form that I want to add, I want them to be equal
> to total hours; but I don't know how.The data types of these fields is
> TEXT.
>
> SET UET Othertime Total Hours
> 05:00 05:00 5:00 15:00[/color]

[cristian sepulveda]

Re: VBA Function?

Thank you Wayne. It works great!



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[James Fortune]

Re: VBA Function?

csepulveda@part ners.org (christian) wrote in message news:<e1cc3cc3. 0404230622.3f91 1f12@posting.go ogle.com>...[color=blue]
> Hi all,
>
> I've four fields in a form that I want to add, I want them to be equal
> to total hours; but I don't know how.The data types of these fields is
> TEXT.
>
> SET UET Othertime Total Hours
> 05:00 05:00 5:00 15:00[/color]

If your time format is hh:nn you can use something like:

'--------------
Public Function AddDurations(st rDuration1 As String, strDuration2 As
String) As String
Dim intHours As Integer
Dim intMinutes As Integer

intHours = Val(Left(strDur ation1,2)) + Val(Left(strDur ation2),2)
intMinutes = Val(Right(strDu ration1,2)) +
Val(Right(strDu ration2, 2))
If intMinutes > 59 Then
intHours = intHours + 1
intMinutes = intMinutes - 60
End If
AddDurations = Format$(intHour s, "00") & ":" & Format$(intMinu tes,
"00")
End Function
'--------------
strHoursWorked = AddDurations(Ad dDurations(strH ours1, strHours2), strHours3)

or rewrite the above to accomodate more durations. Also, test before using.

James A. Fortune