If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Timesheet formula
Hi
I have a form (Access 2007) where I enter weekly hours (present and absent) for 60 employees. Every employee accumulates up to but not more than 480 hours at any moment during his employment period, (accumulates 12 hours every month). If his balance is 480 sick live hours and gets absent, lets say, 32 hours during this week, then his sick leave balance should be 460 hours ([480-32]+[12] at the end of the month. If the employee is never absent, then his sick leave balance should be no more than 480 hours. If he is absent 8 hours this week, then his balance should be 480 [480-8]+[12]. How can this be done in the form? What formula can I place in the query to obtain the same information as above? Any help will be greatly appreciated. Thanks in advance. -- josean |
#2
|
|||
|
|||
Timesheet formula
You might be able to use one or both of the following formulas.
Usage would be like: [LeaveBalance] = MinOf(480, ([LeaveBalance] - [SickTaken]) + [AccumulatedLeave]) Code Start ================== Public Function MinOf(Value1 As Variant, Value2 As Variant) As Variant Const PROC_NAME As String = "MinOf" On Error GoTo ErrorHandler If Value1 Value2 Then MinOf = Value1 Else MinOf = Value2 End If Cleanup: Exit Function ErrorHandler: MsgBox "Error: " & Err.Number & ", " & Err.Description, , MOD_NAME & "." & PROC_NAME On Error Resume Next GoTo Cleanup End Function Public Function MaxOf(Value1 As Variant, Value2 As Variant) As Variant Const PROC_NAME As String = "MaxOf" On Error GoTo ErrorHandler If Value1 Value2 Then MaxOf = Value1 Else MaxOf = Value2 End If Cleanup: Exit Function ErrorHandler: MsgBox "Error: " & Err.Number & ", " & Err.Description, , MOD_NAME & "." & PROC_NAME On Error Resume Next GoTo Cleanup End Function ================== Code End "Jose Anibal" wrote: Hi I have a form (Access 2007) where I enter weekly hours (present and absent) for 60 employees. Every employee accumulates up to but not more than 480 hours at any moment during his employment period, (accumulates 12 hours every month). If his balance is 480 sick live hours and gets absent, lets say, 32 hours during this week, then his sick leave balance should be 460 hours ([480-32]+[12] at the end of the month. If the employee is never absent, then his sick leave balance should be no more than 480 hours. If he is absent 8 hours this week, then his balance should be 480 [480-8]+[12]. How can this be done in the form? What formula can I place in the query to obtain the same information as above? Any help will be greatly appreciated. Thanks in advance. -- josean |
Thread Tools | |
Display Modes | |
|
|