stephensaid
I have a table that contains among other fields the following fields
mployeeID
EmployeeMonHrs
EmployeeTueHrs
EmployeeWedHrs
EmployeeThuHrs
EmployeeFriHrs
EmployeeSatHrs
EmployeeSunHrs
EmployeeBasicPay
What I want to do is divide EmployeeBasicPay with the Total number of hours.
All Hrs fields are formatted as Date/Time (Short Time)
The code below is what I managed upto now. I know there's something wrong with the method used to calculate time but what?
CODE
Function GrossWage(BasicPay As Double, PayFreq As String, EngDate As Date, TermDate As Date, DateFrom As Date, DateTo As Date, MonHrs As Date, TueHrs As Date, WedHrs As Date, ThuHrs As Date, FriHrs As Date, SatHrs As Date, SunHrs As Date) As Double
Dim j As Integer
Dim TotHrsWorked
TotHrsWorked = 0
Flag = ""
If EngDate > DateFrom Then DateFrom = EngDate: Flag = "T"
If TermDate <> 0 Then
If TermDate < DateTo Then DateTo = TermDate: Flag = "T"
End If
If Flag = "T" Then
For j = 1 To 7
DysWorked = WeekdaysCount(DateFrom, DateTo, Right(Str(j), 1)) '<----Can I improve this statement to send a one string character.
If j = 1 Then TotHrsWorked = DysWorked * SunHrs
If j = 2 Then TotHrsWorked = TotHrsWorked + (DysWorked * MonHrs)
If j = 3 Then TotHrsWorked = TotHrsWorked + (DysWorked * TueHrs)
If j = 4 Then TotHrsWorked = TotHrsWorked + (DysWorked * WedHrs)
If j = 5 Then TotHrsWorked = TotHrsWorked + (DysWorked * ThuHrs)
If j = 6 Then TotHrsWorked = TotHrsWorked + (DysWorked * FriHrs)
If j = 7 Then TotHrsWorked = TotHrsWorked + (DysWorked * SatHrs)
Next
GrossWage = (BasicPay / (MonHrs + TueHrs + WedHrs + ThuHrs + FriHrs + SatHrs + SunHrs)) * TotHrsWorked
Exit Function
End If

If PayFreq = "M" Then
GrossWage = (BasicPay * 52 / 12)
ElseIf PayFreq = "F" Then
GrossWage = (BasicPay * 2)
ElseIf PayFreq = "4" Then
GrossWage = (BasicPay * 4)
ElseIf PayFreq = "W" Then
GrossWage = (BasicPay * 1)
Else
MsgBox("Error in Payment Frequency ID Codes. Only M, W, F and 4 are supported.", ,"Incorrect Variables Passed On")
Exit Function
End If
End Function
Function WeekdaysCount(DateFrom As Date, DateTo As Date, WD As String) As Integer
'Counts Weekdays specified by 'WD' where 1 = Sunday, 2 = Monday, etc
Dim Counter As Integer
Dim i As Date
Dim d As Integer
Dim f As Integer

Counter = 0
For f = 1 To Len(WD)
d = Val(Mid(WD, f, 1))
For i = DateFrom To DateTo
If Weekday(i, vbSunday) = d Then Counter = Counter + 1
Next i
Next f
WeekdaysCount = Counter
End Function

Thanks UA
Stephen
R. Hicks
Your table structure is not properly normalized .. and this will create many more problems unless it is fixed.
Each daily entry should be a seperate record in another table and not as repeating fields within a single record in this table ...

RDH
stephensaid
The Time Fields are only a pattern and are not entered daily. Just once when an employee is created. This way I know what the total time per week is and also i have a break down of the hrs worked per day. When I have an exception like engagement date or termination date, I can work out total hours worked and issue wage accordingly.
Thanks
R. Hicks