What is this Expression doing?

[copleyuk]

I have taken on a database and am not sure what bits of it do.

This expression is within a query called "hours paid" used to generate a payroll report.

TotalNumberOfHours: hourspaid([StartTime],[AcctualStartTime],[EndTime],[Duration Hours],[Duration minutes])

StartTime = short time value ie 12:00
ActualStartTime = short time value
EndTime = short time value
Duration Hours = Numeric value ie 1 or 2 etc not time values
Duration Minutes = Numeric value ie 1 or 2 etc not time values

I cannot figure out what is happening as this report is suppose to work out which is the greater value of either booked time which is the total of [StartTime]+[Duration Hours]+[Duration minutes] or actaul time which is the difference between [AcctualStartTim e] and [EndTime].

Any hints on what is actually happening would be greatly appreciated!

Thanks in advance

Carl

[TheSmileyCoder]

I have taken on a database and am not sure what bits of it do.

This expression is within a query called "hours paid" used to generate a payroll report.

TotalNumberOfHo urs: hourspaid([StartTime],[AcctualStartTim e],[EndTime],[Duration Hours],[Duration minutes])

StartTime = short time value ie 12:00
ActualStartTime = short time value
EndTime = short time value
Duration Hours = Numeric value ie 1 or 2 etc not time values
Duration Minutes = Numeric value ie 1 or 2 etc not time values

I cannot figure out what is happening as this report is suppose to work out which is the greater value of either booked time which is the total of [StartTime]+[Duration Hours]+[Duration minutes] or actaul time which is the difference between [AcctualStartTim e] and [EndTime].

Any hints on what is actually happening would be greatly appreciated!

Thanks in advance

Carl

From what you have posted, it seems "hourspaid" is a custom function, written by previous users/developers of the database.

You can find the code by opening the Visual Basic Editor, (Alt-F11), then pressing Find, and simply searching for "hourspaid" (remember to check the box called Current Project").

If you don't understand the code you find, post it here and we can give more details.

[NeoPa]

It looks to me like you should have a function defined in one of your modules called hourspaid().

I suggest you find that and post the code in here (NB. Use the [ CODE ] tags when you do so please).

[copleyuk]

Thanks guys,

I have found the Function which shows what is happening much more clearly.

Within the module there are other functions etc being run, could you guys have a look at the code and let me know what you can make sense of please? :)

Public Function hourspaid(st As Variant, AST As Variant, Et As Variant, hr As Integer, min As Integer) As Single
On Error GoTo setAsZero
If IsDate(st) Then st = TimeValue(st) Else GoTo setAsZero
If IsDate(AST) Then AST = TimeValue(AST) Else GoTo setAsZero
If IsDate(Et) Then Et = TimeValue(Et) Else GoTo setAsZero
timebooked = hr + (min / 60)
timeworked = (Et - AST) * 24
If Et < AST Then timeworked = (Et + 1 - AST) * 24

If timebooked > timeworked Then hourspaid = timebooked Else hourspaid = timeworked
If st < AST Then hourspaid = timeworked
hourspaid = Int((hourspaid - 0.0000001) * -4) / -4
If hourspaid < 1 Then hourspaid = 1
Exit Function

setAsZero:
Err.Clear
hourspaid = 0
Exit Function
End Function


Public Function sunen(st As Variant, AST As Variant, Et As Variant, hr As Integer, min As Integer, apptdate As Date)
'calculates sunday enhancement
'On Error GoTo setAsZero
'If IsDate(st) Then st = TimeValue(st) Else GoTo setAsZero
'If IsDate(AST) Then AST = TimeValue(AST) Else GoTo setAsZero
'If IsDate(Et) Then Et = TimeValue(Et) Else GoTo setAsZero

If Weekday(apptdate) = 1 And Et >= AST Then sunen = hourspaid(st, AST, Et, hr, min)
If Weekday(apptdate) = 1 And Et < AST Then sunhours = (1 - AST) * 24
If Weekday(apptdate) = 7 And Et < AST Then sunhours = Et * 24
If sunhours >= hourspaid(st, AST, Et, hr, min) / 2 Then sunen = hourspaid(st, AST, Et, hr, min)
If sunen = 0 And sunhours < hourspaid(st, AST, Et, hr, min) / 2 Then sunen = Int((sunhours - 0.0000001) * -4) / -4
If sunen < 1 And sunen > 0 Then sunen = 1
If apptdate = "25/12/2009" Then sunen = hourspaid(st, AST, Et, hr, min)
If apptdate = "28/12/2009" Then sunen = hourspaid(st, AST, Et, hr, min)
If apptdate = "01/01/2010" Then sunen = hourspaid(st, AST, Et, hr, min)
If apptdate = "02/04/2010" Then sunen = hourspaid(st, AST, Et, hr, min)
If apptdate = "05/04/2010" Then sunen = hourspaid(st, AST, Et, hr, min)
If apptdate = "03/05/2010" Then sunen = hourspaid(st, AST, Et, hr, min)
If apptdate = "31/05/2010" Then sunen = hourspaid(st, AST, Et, hr, min)
If apptdate = "30/08/2010" Then sunen = hourspaid(st, AST, Et, hr, min)
If apptdate = "27/12/2010" Then sunen = hourspaid(st, AST, Et, hr, min)
If apptdate = "28/12/2010" Then sunen = hourspaid(st, AST, Et, hr, min)
If apptdate = "03/01/2011" Then sunen = hourspaid(st, AST, Et, hr, min)

Exit Function
setAsZero:
Err.Clear
sunen = 0
Exit Function

End Function

Public Function saten(st As Variant, AST As Variant, Et As Variant, hr As Integer, min As Integer, apptdate As Date)

'On Error GoTo setAsZero
'If IsDate(st) Then st = TimeValue(st) Else GoTo setAsZero
'If IsDate(AST) Then AST = TimeValue(AST) Else GoTo setAsZero
'If IsDate(Et) Then Et = TimeValue(Et) Else GoTo setAsZero

If Weekday(apptdate) = 7 And Et >= AST Then saten = hourspaid(st, AST, Et, hr, min)
If Weekday(apptdate) = 7 And Et < AST Then saten = hourspaid(st, AST, Et, hr, min) - sunen(st, AST, Et, hr, min, apptdate)
If Weekday(apptdate) = 6 And Et < AST Then sathours = Et * 24
If sathours >= hourspaid(st, AST, Et, hr, min) / 2 Then saten = hourspaid(st, AST, Et, hr, min)
If saten = 0 And sathours < hourspaid(st, AST, Et, hr, min) / 2 Then saten = Int((sathours - 0.0000001) * -4) / -4
If saten < 1 And saten > 0 Then saten = 1
Exit Function
setAsZero:
Err.Clear
saten = 0
Exit Function

End Function

Public Function unsocen(st As Variant, AST As Variant, Et As Variant, hr As Integer, min As Integer, apptdate As Date)

'On Error GoTo setAsZero
'If IsDate(st) Then st = TimeValue(st) Else GoTo setAsZero
'If IsDate(AST) Then AST = TimeValue(AST) Else GoTo setAsZero
'If IsDate(Et) Then Et = TimeValue(Et) Else GoTo setAsZero

Select Case Weekday(apptdate)
'starts on sunday
Case Is = 1
If sunen(st, AST, Et, hr, min, apptdate) = hourspaid(st, AST, Et, hr, min) Then
unsocen = 0
GoTo EndOfCase
End If
If Et * 24 < 6 Then unsoctime = Et * 24 Else unsoctime = 6
If Et * 24 > 20 Then unsoctime = Et * 24 - 20 + 6
If unsoctime + sunen(st, AST, Et, hr, min, apptdate) >= hourspaid(st, AST, Et, hr, min) / 2 Then
unsocen = hourspaid(st, AST, Et, hr, min) - sunen(st, AST, Et, hr, min, apptdate)
Else: unsocen = Int((unsoctime - 0.0000001) * -4) / -4
End If
EndOfCase:
'starts sat
Case Is = 7
unsocen = 0
'starts fri
Case Is = 6
If AST <= Et Then
If AST * 24 < 6 Then
If Et * 24 > 6 Then unsoctime = 6 - AST * 24 Else unsoctime = (Et - AST) * 24
If AST = Et Then unsoctime = 1
End If
If Et * 24 > 20 Then
If AST * 24 < 20 Then unsoctime = unsoctime + Et * 24 - 20 Else unsoctime = (Et - AST) * 24
If AST = Et Then unsoctime = 1
End If
If unsoctime > hourspaid(st, AST, Et, hr, min) / 2 Then
unsocen = hourspaid(st, AST, Et, hr, min)
Else: unsocen = Int((unsoctime - 0.0000001) * -4) / -4
End If
If unsocen < 1 And unsocen > 0 Then unsocen = 1
Else
If AST * 24 < 6 Then unsoctime = 6 - AST * 24 + 4 Else
If AST * 24 < 20 Then unsoctime = 4 Else unsoctime = 24 - Et * 24
If unsoctime + saten(st, AST, Et, hr, min, apptdate) >= hourspaid(st, AST, Et, hr, min) / 2 Then
unsocen = hourspaid(st, AST, Et, hr, min) - saten(st, AST, Et, hr, min, apptdate)
Else: unsocen = Int((unsoctime - 0.0000001) * -4) / -4
End If
End If
'monday - thursay
Case Else
If AST * 24 < 6 And AST <= Et Then
If Et * 24 > 6 Then unsoctime = 6 - AST * 24 Else unsoctime = (Et - AST) * 24
If AST = Et Then unsoctime = 1
End If
If Et * 24 > 20 And AST <= Et Then
If AST * 24 < 20 Then unsoctime = unsoctime + Et * 24 - 20 Else unsoctime = (Et - AST) * 24
If AST = Et Then unsoctime = 1
End If
If AST > Et Then
If AST * 24 < 6 Then unsoctime = 6 - AST * 24
If AST * 24 <= 20 Then unsoctime = unsoctime + 4 Else unsoctime = unsoctime + 24 - AST * 24
If Et * 24 < 6 Then unsoctime = unsoctime + Et * 24 Else unsoctime = unsoctime + 6
If Et * 24 > 20 Then unsoctime = unsoctime + Et * 24 - 20
End If
If unsoctime > hourspaid(st, AST, Et, hr, min) / 2 Then
unsocen = hourspaid(st, AST, Et, hr, min)
Else: unsocen = Int((unsoctime - 0.0000001) * -4) / -4
End If
If unsocen < 1 And unsocen > 0 Then unsocen = 1

End Select

Exit Function
setAsZero:
Err.Clear
unsocen = 0
Exit Function


End Function




Public Function sameNextday(tkndate As Date, aptdate As Date)
'function to show if appt is a same day or next day booking

dd = DateDiff("d", tkndate, aptdate)
If dd <= 1 Then sameNextday = True Else: sameNextday = False
If dd <= 3 And Weekday(tkndate) = 6 Then sameNextday = True

End Function

I understand the first section which is what I was expecting to see and the second section is listing bank holiday dates to increase the pay rates.
Other than that I'm kinda at a loss! Any input would be great...

Thanks again!

[NeoPa]

I'm sorry Carl. That's not how it works. You post a specific question and we help where we can.

Is there any question you still have with the hourspaid() function code?

[copleyuk]

Sorry, My bad.

No thats evreything relating to the original post.

Thanks very much guys :)

[TheSmileyCoder]

I think I will use that bit of code as a horror-example of why developers should always remember to comment their code.

[patjones]

I think I will use that bit of code as a horror-example of why developers should always remember to comment their code.

I think we should use it to make a documentary called If Statements Gone Wild!