Some help with a function I created for calculating Flexi Time.

[Shingouki]

Updated : Code for calculating Flexi Time

Information about the way in which flexible hours are calculated is annotated in the opening comments.

tbl_timesheet
timesheet_no (Autonumber) "Primary Key"
staff_member (number) "Foreign key with staff table"
date (Date/Time) "Date of Work"
t_in (Date/Time) "Time Into work"
l_out (Date/Time) "Time out for lunch"
l_in (Date/Time) "Time Back from lunch"
t_out (Date/Time) "Time out from work"
sick_day (number) "hours off sick for this day, includes"
flexi_time (number) "flexible time taken that day"
training (number) "time spent training"
holiday (yes/no) "was this a leave day"

[COLOR="seagreen"]
'---------------------------------------------------------------------------------------------
'(c) Jack Lingwood 20th September 2006
'
'This function calculates the amount of flexi time accrued during a single day for a member of staff.
'It is based on the following parameters.
'
'Time in, Time out, Lunch In, Lunch out. These are the actual hours worked by the employee. For the purposes of
'calculating flexi time only work done between the hours of 07:30am and 19:00pm can be counted.
'A lunch break of minium 30mins must be taken between the hours of 12:00 - 14:00.
'This may be anywhere between 30mins minimum and 2 hours maximum.
'
'As well as the above variables there are four other factors that should be taken into consideration when
'calculating whether or not flexi hours have been accrued for the day.
'
'Sick Leave - Hours entered count as normal working hours and should be included in the calculation
'Training - Hours entered count as normal working hours and should be included in calculation
'Holiday - If set to true, counts as a normal 7 hour working day.
'Flexi - Hours entered count as normal working hours and should be included in calculation
'
'All these variables are used to calculate the amount of flex time accrued for the day.
'I will also need to add default variables to Saturday + Sunday to ensure they don't count against flexible hours.
'--------------------------------------------------
[/COLOR] 

Function DailyFlexiTime(timesheet_date, t_in, l_out, l_in, t_out, sick_hours, training_hours, flexi_hours, holiday)
[COLOR="seagreen"] 
'---------------------------------------------------------------------------------------------
'Variable declarations
'---------------------------------------------------------------------------------------------
'--------------------------------------------------
'Constants
'--------------------------------------------------
[/COLOR] 

Time_In_Boundary = #7:30:00 AM# 'The earliest time employees may begin working
Time_Out_Boundary = #7:00:00 PM# 'The latest time employees may end working
Lunch_In_Boundary = #2:00:00 PM# 'The latest time employees may return from lunch
Lunch_out_Boundary = #12:00:00 PM# 'The earliest permissable time employees may go for lunch
Default_Variable = #12:00:00 AM# 'The default time used for catching empty fields
default_hours = 7 'No of hours in a standard working day
Minimum_Lunch = 0.5 'Minimum amount of hours employee may take for lunch
Maximum_Lunch = 2  'Maximum amount of hours employee may take for lunch


[COLOR="seagreen"] 
'--------------------------------------------------
'Calculated Variables
'--------------------------------------------------
[/COLOR] 
Dim hours_worked As Double
Dim lunch_hours As Double
Dim late_lunch As Double
Dim early_lunch As Double
Dim unnapproved_abscence As Double
Dim flexi_accrued_today As Double

[COLOR="seagreen"] 
'--------------------------------------------------
'Set calculation limits.
'--------------------------------------------------
'timesheet_date = #9/20/2006# follows this format
[/COLOR]
 
Dayofweek = DatePart("w", timesheet_date) 'ascertain which day the date falls on

If Dayofweek = 1 Or Dayofweek = 7 Then 'catch whether it is a weekend or weekday

[COLOR="seagreen"]'by default staff aren't required to work weekends and thus default hours must be set to 0[/COLOR] 
    default_hours = 0
    flexi_accrued_today = 0
	    If Dayofweek = 1 Then
    DailyFlexiTime = "0"

    ElseIf Dayofweek = 7 Then
	    DailyFlexiTime = "0"
    End If

Else: [COLOR="seagreen"] 'Not a weekend so treat as a working day
  
 [COLOR="seagreen"]'no times entered so catch if its holiday / flexi leave etc...
    'If holiday is flagged true this is approved leave and will count as a full working day of 7 hours
  If holiday = True Then
        holiday_hours = 7
  Else: holiday_hours = 0
  End If
  
  positive_hours = holiday_hours + training_hours + sick_hours
  DailyFlexiTime = positive_hours - default_hours


[COLOR="seagreen"] 'if any of these times are set to 0:00 despite others being set then flag an error and inform the user[/COLOR]
  If Not (t_in = Default_Variable And t_out = Default_Variable And l_in = Default_Variable And l_out = Default_Variable) Then
   [COLOR="seagreen"]  'Catch which field is not set[/COLOR]
    If (t_in = Default_Variable Or t_out = Default_Variable Or l_in = Default_Variable Or l_out = Default_Variable) Then
        If t_in = Default_Variable Then
            DailyFlexiTime = "Time in missing"
        End If
    
        If t_out = Default_Variable Then
            DailyFlexiTime = "Time out missing"
        End If
    
        If l_in = Default_Variable Then
            DailyFlexiTime = "Lunch in missing"
        End If
    
        If l_out = Default_Variable Then
            DailyFlexiTime = "Lunch out missing"
        End If
     Else:
    
[COLOR="seagreen"]'Ok Everything seems ok so lets execute the main function...[/COLOR]
[COLOR="seagreen"]'On weekedays the required hours to work = 7[/COLOR]
      
[COLOR="seagreen"]'If holiday is flagged true this is approved leave and will count as a full working day of 7 hours[/COLOR]
        If holiday = True Then
            holiday_hours = 7
        Else: holiday_hours = 0
        End If
    
[COLOR="seagreen"]'hours worked after 18.00pm not part of flexi-time ignore if 0:00[/COLOR]
        If t_out > Time_Out_Boundary And t_out <> Default_Variable Then
            t_out = Time_Out_Boundary
        Else: t_out = t_out
        End If
    
[COLOR="seagreen"]'hours worked before 8.00am not part of flexi-time[/COLOR]
        If t_in < Time_In_Boundary And t_in <> Default_Variable Then
            t_in = Time_In_Boundary
        Else: t_in = t_in
        End If
    
[COLOR="seagreen"]'calculate hours worked[/COLOR]
        hours_worked = DateDiff("n", t_in, t_out)
        hours_worked = hours_worked / 60 'convert to hours
[COLOR="seagreen"]'calculate lunch taken[/COLOR]
        lunch_hours = DateDiff("n", l_out, l_in)
        lunch_hours = lunch_hours / 60 'convert to hours
[COLOR="seagreen"]'If there is no value for the lunch hour ignore it[/COLOR]
     
        If l_out <> Default_Variable And l_in <> Default_Variable Then
[COLOR="seagreen"]'if lunch hour falls outside the allowed range set lunch break to 0.5 hours[/COLOR]
          If l_out >= Lunch_In_Boundary Or l_in <= Lunch_out_Boundary Then
            lunch_hours = Minimum_Lunch
          End If
       
[COLOR="seagreen"]         'minimum lunch of 30 minutes must be used[/COLOR]
            If lunch_hours < Minimum_Lunch Then
                lunch_hours = Minimum_Lunch
[COLOR="seagreen"]         'maximum period of lunch is 2 hours[/COLOR]
            ElseIf lunch_hours > Maximum_Lunch Then
                lunch_hours = Maximum_Lunch
            Else: lunch_hours = lunch_hours
            End If
        End If
    
[COLOR="seagreen"]'now we need to catch any time that fell outside the designated lunch hours[/COLOR]
[COLOR="seagreen"]'this then needs to be deducted from the days working hours.[/COLOR]
    
        If l_in > Lunch_In_Boundary And l_in <> Default_Variable Then
            late_lunch = DateDiff("n", Lunch_In_Boundary, l_in)
            late_lunch = late_lunch / 60 'convert to hours
        Else: late_lunch = 0
        End If
    
        If l_out < Lunch_out_Boundary And l_out <> Default_Variable Then
            early_lunch = DateDiff("n", l_out, Lunch_out_Boundary)
            early_lunch = late_lunch / 60 'convert to hours
        Else: early_lunch = 0
        End If
[COLOR="seagreen"]'add together all lunch time outside the permissable hours[/COLOR]
        unapproved_abscence = (late_lunch) + (early_lunch)
    
[COLOR="seagreen"]'Now that we have all of the necessary variables and have restricted them to the different boundaries[/COLOR]
[COLOR="seagreen"]'We can calculate the amount of flexibile hours that have been accrued for the date passed to the function[/COLOR]
        positive_hours = hours_worked + holiday_hours + training_hours + sick_hours
        negative_hours = lunch_hours + unapproved_abscence + default_hours
    
        DailyFlexiTime = positive_hours - negative_hours
      End If
    End If
End If
End Function

2nd Function for setting limits to total accrued flexible time.

Function flexi_check(accrued_flexi)
[COLOR="seagreen"]'Provide information to the user about the hours they have accrued.[/COLOR]
Maximum_Flexible_Accruable = 7 [COLOR="seagreen"]'The maximum accruable flexible hours that may be transferred to the next period[/COLOR]
Minimum_Flexible_Accruable = -7 [COLOR="seagreen"]'The minimum accruable flexible hours that may be transferred to the next period[/COLOR]
  
    If accrued_flexi > Maximum_Flexible_Accruable Then
    flexi_check = "Have a gold star"
    ElseIf accrued_flexi < Minimum_Flexible_Accruable Then
    flexi_check = "No Chocolate for you"
    End If

End Function

 

[Uncle Gizmo]

Clarification please

Some very interesting code you have written there, which language is it written in?

I am having a little difficulty, in particular please could you explain the variable "response".

 

[Shingouki]

Hi sorry I should have clarified, the *response* variable is actually unecessary I just used it during testing as I was a little unfamiliar with the VB debugger. I've figured out how to use the debugger properly now so it's a bit unecessary now. As I was not sure how to pass actual table data through the function I just used a macro to execute manually entered variables into the function so that I could test if It was generating the desired results.

Ideally what I want to happen is for the function to return the value stored in

flexi_accrued_today

so that I can simply create a text box on a form with it's control source set as =DailyFlexiTime()

I could be going about this in totally the wrong way. The language as far as I'm aware is Visual Basic. As stated above though this is my first ever attempt at writing in the language so to be honest I just guessed at 90% of the syntax.

 

[Uncle Gizmo]

I am new to VBA and basically wrote this module with no real knowledge of VBA

Earlier you suggested it was VBA and now you suggest it's VB. I suspect it is from a VB project. Needs clarifying really.

Aside from the "response" variable which is undeclared, you also have the following undeclared variables:

'//////////'Time_In_Boundary = #7:30:00 AM# 'The earliest time employees may begin working
'//////////'Time_Out_Boundary = #7:00:00 PM# 'The latest time employees may end working
'///////////'Lunch_In_Boundary = #2:00:00 PM# 'The latest time employees may return from lunch
'///////////'Lunch_out_Boundary = #12:00:00 PM# 'The earliest permissable time employees may go for lunch
'///////////'Default_Variable = #12:00:00 AM# 'The default time used for catching empty fields
'///////////'Minimum_Lunch = 0.5 'Minimum amount of hours employee may take for lunch
'////////////'Maximum_Lunch = 2 'Maximum amount of hours employee may take for lunch
'////////////'Maximum_Flexible_Accruable = 7 'The maximum accruable flexible hours that may be transferred to the next period
'////////////'Minimum_Flexible_Accruable = -7 'The minimum accruable flexible hours tha

(and a few more) Please could you explain these as well!

cheers Tony

 

[Shingouki]

Sorry about the nomenclature confusion *told you I was new to all this *. Basically all i did was create a new module in access, as far as I know the modules in access are written in Visual Basic? when i said VBA that is what i meant... is this wrong?

As for the undeclared variables. As stated in the previous post you can ignore "response" it's no longer necessary.

The other variables you included in there are just local constants, I put these in so that I wouldn't need to put actual values into all my if statements. This is helpful in case the rules for flexi time change. For example if the flexible hours were extended to allow staff to enter at 6:00 am, all you would need to do is change the "Time_In_Boundary" to #6:00:00 AM#. There would be no need to change the variable ni my if statement

The reason I didn't declare them is that it seemed to be doing it for me. These are only constants to be used locally. Is there any harm in doing it this way?

 

[Shingouki]

Sorry if Bumping threads isn't allowed round here. Just hoping someone can help...

The key thing I really need to understand is how do I pass table data into a function in an access module.

EDIT -----

Never mind I figured out that what I was trying to do is totally unnecessary, I have changed the function a little and am now just calling it as on the form I wanted to. I will post what I did later in case it's of use to anyone else.