Module to determine day and location of shift

[Elmobram22]

Hey,

I need a module but haven't got the foggiest on where to start. I have a query that works out whether a staff member has done a sleep in based on their clocking out time (TIME OUT). I also have a column for DAY IN which it stores the day they logged in and HOUSE which indicates which house they clocked in at. What I want to do is determine whether each 'sleep in' is a weekday or weekend shift and at which house. We have different rates of pay based on the houses and whether it is weekend or not. Any help would be much appreciated.

Cheers,

Elmobram22

 

[pr2-eugin]

You can use the Weekday function to determine the Day of the week, when provided with a Date. Since you already have the information of which house they clock in, I think you can club them together to get your result in a simple Query, does not need a Module.

 

[Elmobram22]

Hi,

Not sure on how that would work as there are a few variables eg.

If the house = "AV" and its a Weekday and the Sleep In column is "Sleep In" then it should equal "V". If its a Weekend and the above it would equal "A".

If the house = "LH" and its a Weekday and the Sleep In column is "Sleep In" then it should equal "H". If its a Weekend and the above it would equal "L".

If the house = "PS" and its a Weekday and the Sleep In column is "Sleep In" then it should equal "S". If its a Weekend and the above it would equal "P".

I also want to be able to hold a currency value for each one in the next column if possible. I have attached the database for anyone to have a look at.

Cheers,

Elm

 

[pr2-eugin]

Based on the limited information, I think it calls for a function, the following should be the way to go..

Public Function findPay(houseStr As String, sleepStr As Variant, dayIn As String) As String
[COLOR=Green]'********************
'Code Courtesy of
'  Paul Eugin
'********************[/COLOR]
    If Len(sleepStr & vbNullString) = 0 Then
        findPay = vbNullString
    ElseIf sleepStr = "Sleep In"
        Select Case dayIn
            Case "Saturday", "Sunday"
                findPay = Left(houseStr, 1)
            Case Else
                findPay = Right(houseStr, 1)
        End Select
    Else
        findPay = vbNullString
    End If
End Function

Although I do not get what you mean by..

I also want to be able to hold a currency value for each one in the next column if possible.....

 

[Elmobram22]

Nice one pr2-Eugine, that has done what I wanted. Good work as always!!

 

[pr2-eugin]

Thanks for the compliment. Happy to help.