Full Version: Time Addition
UtterAccess Forums > Microsoft® Access > Access Date + Time
I have a table that contains among other fields the following fields
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?
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)
        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)
        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
R. Hicks
You need to address your much bigger problem ...
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 ...

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.
R. Hicks
Post your table structure ...
I have posted the db here
also have worked out how to workaround this isse but I'm afraid it does not look so preffessional so I you can figure out some better way I'll appreciate it.
I have written a function that converts hrs/min entered in a Date/Time field into decimals so that I can do calculations with them and issue Gross Wage.
Thanks Stephen
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.