470,644 Members | 1,202 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,644 developers. It's quick & easy.

VBA Function?

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
3 2268
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
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
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.

Similar topics

3 posts views Thread by domeceo | last post: by
5 posts views Thread by phil_gg04 | last post: by
2 posts views Thread by laredotornado | last post: by
2 posts views Thread by sushil | last post: by
8 posts views Thread by Olov Johansson | last post: by
3 posts views Thread by Beta What | last post: by
1 post views Thread by Korara | last post: by
reply views Thread by warner | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.