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

VBA Function?

P: n/a
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
Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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(Right([SET], 2))+CInt(Right([UET], 2))+CInt(Right([OtherTime],
2))))\60, "00") & ":" & Format(((CInt(Left([SET], 2))+CInt(Left([UET],
2))+CInt(Left([OtherTime], 2)))*60+(CInt(Right([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" <cs********@partners.org> wrote in message
news:e1**************************@posting.google.c om...
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

Nov 12 '05 #2

P: n/a
Thank you Wayne. It works great!

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

P: n/a
cs********@partners.org (christian) wrote in message news:<e1**************************@posting.google. com>...
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


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

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

intHours = Val(Left(strDuration1,2)) + Val(Left(strDuration2),2)
intMinutes = Val(Right(strDuration1,2)) +
Val(Right(strDuration2, 2))
If intMinutes > 59 Then
intHours = intHours + 1
intMinutes = intMinutes - 60
End If
AddDurations = Format$(intHours, "00") & ":" & Format$(intMinutes,
"00")
End Function
'--------------
strHoursWorked = AddDurations(AddDurations(strHours1, strHours2), strHours3)

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

James A. Fortune
Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.