Calculate Worked Days Minus Weekends and My Holidays 1

[oxicottin]

This is in reference to thread702-1723375: Yearly calendar for attendance Majp helped me build that was closed. I'm now asked by my all my superior a printout of the troubled employees worked days to date for the year since they dont like to come to work and take days off constantly. Anyways, he wanted me to calculate for the year not including Saturdays, Sundays, Absences, and OUR Holidays which is in [tbl_Holidays].

I figued I could have used a query but I dont know how to get [tbl_Holidays] linked in a query with [tbluEmployees],[tbluAbsenceCodes] and [tbl_YearCalendar]. Kinda like the query qry_rpt_AbsencesForYear where it feeds off of the main forms "Year" and employee selected.

Anyways, how would the code go or even if I can do a query would be nice.... Thanks!

Thanks,
SoggyCashew.....

 

[MajP]

Sorry. Been or travel or working too hard. If you still need to do this.

I would build a functions that you can pass in two dates from a query. The function I built is a brute force looping of the days. You probably could write a cleaner code that count the weeks and subtracts the number of weekends. Then counts the holidays and counts if any holidays are also weekends. This would be much faster, but you will have to be precise in logic. This should run as long as the number of people and the period checked are not too large.

Public Function WorkDaysBetween(StartDate As Date, EndDate As Date) As Integer
  Dim CurrentDay As Date
  CurrentDay = CDate(Int(StartDate))
  Do
    If Weekday(CurrentDay) <> vbSaturday And Weekday(CurrentDay) <> vbSunday And Not IsHoliday(CurrentDay) Then
      WorkDaysBetween = WorkDaysBetween + 1
    End If
    CurrentDay = CurrentDay + 1
  Loop Until CurrentDay > EndDate
End Function

Public Function IsHoliday(CurrentDay As Date) As Boolean
  IsHoliday = (DCount("*", "tbl_Holidays", "HolidayDate = #" & Format(CurrentDay, "mm/dd/yyyy" & "#")) = 1)
End Function

Public Function AbsenceBetween(StartDate As Date, EndDate As Date, empID As Long) As Integer
 Dim strStart As String
 Dim strEnd As String
 Dim strWhere As String
 strStart = "#" & Format(StartDate, "mm/dd/yyyy") & "#"
 strEnd = "#" & Format(EndDate, "mm/dd/yyyy") & "#"
 strWhere = "EmployeeID = " & empID & " AND Absencedate BETWEEN " & strStart & " AND " & strEnd
 'Debug.Print strWhere
 AbsenceBetween = DCount("*", "tbl_YearCalendar", strWhere)
End Function

Public Function DaysWorkedBetween(StartDate As Date, EndDate As Date, empID As Long) As Integer
  DaysWorkedBetween = WorkDaysBetween(StartDate, EndDate) - AbsenceBetween(StartDate, EndDate, empID)
End Function

in a query you can hardwire the dates or reference a location on a form.

SELECT tbluEmployees.EmpFName, tbluEmployees.EmpLName, AbsenceBetween(#1/1/2017#,#12/31/2017#,[EmployeeID]) AS Absences, DaysWorkedBetween(#1/1/2017#,#12/31/2017#,[EmployeeID]) AS DaysWorked
FROM tbluEmployees;

[/code ]
[tt]EmpFName EmpLName	Absences DaysWorked
Bryan	Boob	        2	250
Chad	Zandell	        0	252
Jim	Ruffles	        0	252
Beak	Heart	        0	252
Edward	Scissorhands	0	252
Ed	Fabe	        0	252
Rick	Radon	        0	252[/tt]

 

[oxicottin]

MaJP, yes I still need this... I was patiently waiting . Question though... In the query for a criteria how can I use the forms [frm_YearCalendar] combo box [cboYear] to figure out its first day of that year as a start date and todays date as my end date?

The bad thing here is your way is great if I run a report for a whole year like last year but the above way is only good if im running a report on a year that isn't finished and I need the exact days worked. It probably would be beast if I had a date picker with a start and end date. How would I go about doing that in the query?


Thanks,
SoggyCashew.....

 

[MajP]

Properly designed code is always flexible and never hard coded. My functions can be passed any dates and therfore can get reused in many ways. So with this design you can build "wrapper" functions to go around your functions and answer a specific question.

Public Function WorkDaysFromYear() As Integer
  Dim StartDate As Date
  Dim endDate As Date
  StartDate = DateSerial(Forms("Year_Calendar").cboYear, 1, 1)
  endDate = Date
  WorkDaysFromYear = WorkDaysBetween(StartDate, endDate)
End Function
Public Function AbsenceFromYear(empID) As Integer
  Dim StartDate As Date
  Dim endDate As Date
  StartDate = DateSerial(Forms("Year_Calendar").cboYear, 1, 1)
  endDate = Date
  AbsenceFromYear = AbsenceBetween(StartDate, endDate, empID)
End Function

 

[oxicottin]

Thanks MaJP that's smart, how would I reference WorkDaysFromYear and AbsenceFromYear(empID) in my query?

Thanks,
SoggyCashew.....

 

[MajP]

select workdaysFromYear() as Workdays, absenceFromYear([employeeID]) as Absences, ....

 

[oxicottin]

MaJP, I tried that and been goofing with it for afew days and for the life of me I cant get it to work. I did see you had Year_Calendar for the form and it was frm_YearCalendar but I cant figure out why it wont work.

I have the frm_YearCalendar open and run the query and it asks for a parameter value for WorkDaysFromYear which it shouldn't because it should be getting it from my open forms combo box right? Next I am getting another box asking for a parameter value empID and I leave it blank and I get an error Compile error: ByRef argument type mismatch and it takes me to the empID

AbsenceFromYear = AbsenceBetween(StartDate, endDate, [highlight #729FCF]empID[/highlight])



Thanks,
SoggyCashew.....

 

[MajP]

Public Function WorkDaysBetween(StartDate As Date, endDate As Date) As Integer
  Dim CurrentDay As Date
  CurrentDay = CDate(Int(StartDate))
  Do
    If Weekday(CurrentDay) <> vbSaturday And Weekday(CurrentDay) <> vbSunday And Not IsHoliday(CurrentDay) Then
      WorkDaysBetween = WorkDaysBetween + 1
    End If
    CurrentDay = CurrentDay + 1
  Loop Until CurrentDay > endDate
End Function

Public Function IsHoliday(CurrentDay As Date) As Boolean
  IsHoliday = (DCount("*", "tbl_Holidays", "HolidayDate = #" & Format(CurrentDay, "mm/dd/yyyy" & "#")) = 1)
End Function

Public Function AbsenceBetween(StartDate As Date, endDate As Date, empID As Variant) As Integer
 Dim strStart As String
 Dim strEnd As String
 Dim strWhere As String
 If IsDate(StartDate) And IsDate(endDate) And Not IsNull(empID) Then
    strStart = "#" & Format(StartDate, "mm/dd/yyyy") & "#"
    strEnd = "#" & Format(endDate, "mm/dd/yyyy") & "#"
    strWhere = "EmployeeID = " & empID & " AND Absencedate BETWEEN " & strStart & " AND " & strEnd
    'Debug.Print strWhere
    AbsenceBetween = DCount("*", "tbl_YearCalendar", strWhere)
  End If
End Function

Public Function DaysWorkedBetween(StartDate As Date, endDate As Date, empID As Long) As Integer
  DaysWorkedBetween = WorkDaysBetween(StartDate, endDate) - AbsenceBetween(StartDate, endDate, empID)
End Function

Public Function WorkDaysFromYear() As Integer
  Dim StartDate As Date
  Dim endDate As Date
  If CurrentProject.AllForms("frm_YearCalendar").IsLoaded Then
    StartDate = DateSerial(Forms("frm_YearCalendar").cboYear, 1, 1)
    endDate = Date
    WorkDaysFromYear = WorkDaysBetween(StartDate, endDate)
  End If
End Function
Public Function AbsenceFromYear(empID As Variant) As Integer
  Dim StartDate As Date
  Dim endDate As Date
  If Not IsNull(empID) Then
    If CurrentProject.AllForms("frm_YearCalendar").IsLoaded Then
      StartDate = DateSerial(Forms("frm_YearCalendar").cboYear, 1, 1)
      endDate = Date
      AbsenceFromYear = AbsenceBetween(StartDate, endDate, empID)
    Else
      Debug.Print "Form not loaded"
    End If
  End If
End Function

The error was in absencesfromyear the parameter did not have a defined datatype
Public Function AbsenceFromYear(empID) As Integer
It therefore defaults as a variant
I then pass it to another function where the empID is declared as a long. This is where you get the datatype mismatch. You pass a function a dattype that is not what the function expects.
I made all the parameters into variants. I do this for queries because it is likely that the query will pass a null value if there is a field that has not been filled in. This will then lock up the query. So I make them variants and check to see if they are null or not. I also added some error checking to ensure the form is loaded. If not you get an error.

New SQL would be

SELECT 
 tbluEmployees.EmpFName, 
 tbluEmployees.EmpLName, 
 absencefromYear([employeeID]) AS Absences, 
 workdaysFromYear() AS WorkDays, 
 [workdays]-[absences] AS DaysWorked
FROM 
 tbluEmployees

;

 

[oxicottin]

That worked perfect.... Thanks Again!

Thanks,
SoggyCashew.....

 

[oxicottin]

MaJP, I wanted to display results other than in a report. I have been trying to display a single result to show how many missed days ect on a subform for the current employee on the frm_YearCalendar and I cant seem to get results in my subform but I get them in my query for a single user. I am getting a Run-time error 94 invalid use of Null and its taking me back to Public Function WorkDaysFromYear()

[highlight #FCE94F]StartDate = DateSerial(Forms("frm_YearCalendar").cboYear, 1, 1)[/highlight]

I have my subform link master as [cboEmployee] and its link Child fields [EmployeeID] so it populates when the employee name is selected.

The query im using is:

SELECT tbluEmployees.EmployeeID, tbluEmployees.EmpFName, tbluEmployees.EmpLName, [EmpLName] & ", " & [EmpFName] AS EmployeeName, absencefromYear([employeeID]) AS Absences, workdaysFromYear() AS WorkDays, [workdays]-[absences] AS DaysWorked
FROM tbluEmployees
WHERE (((tbluEmployees.EmployeeID)=[forms]![frm_YearCalendar]![cboEmployee]))
GROUP BY tbluEmployees.EmployeeID, tbluEmployees.EmpFName, tbluEmployees.EmpLName;

Thanks,
SoggyCashew.....

 

[MajP]

I do not understand your query. If the query is the subform and it is linked then you do not need a where clause and why the group by. simply

SELECT 
 tbluEmployees.EmployeeID, 
 tbluEmployees.EmpFName, 
 tbluEmployees.EmpLName, 
 [EmpLName] & ", " & [EmpFName] AS EmployeeName, 
 absencefromYear([employeeID]) AS Absences, 
 workdaysFromYear() AS WorkDays, 
 [workdays]-[absences] AS DaysWorked
FROM 
 tbluEmployees

When a form loads it loads from inside out. The subform loads first then the main form. The query calls cbo.year before it has a value.

You could put in error checking to resume next.

Public Function AbsenceFromYear(empID As Variant) As Integer
  Dim StartDate As Date
  Dim endDate As Date
  If Not IsNull(empID) Then
    If CurrentProject.AllForms("frm_YearCalendar").IsLoaded Then
      if isnull(Forms("frm_YearCalendar").cboYear) then
        startdate = dateserial(year(now),1,1)
      else
        StartDate = DateSerial(Forms("frm_YearCalendar").cboYear, 1, 1)
      end if
      endDate = Date
      AbsenceFromYear = AbsenceBetween(StartDate, endDate, empID)
    Else
      Debug.Print "Form not loaded"
    End If
  End If
End Function

You will also have to requery the subform in the afterupdate event of cboYear.

 

[oxicottin]

MaJP, here is a updated public version with what I was talking about. I got it working with your fixes. Thank You.

Thanks,
SoggyCashew.....