INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Calculate Worked Days Minus Weekends and My Holidays

Calculate Worked Days Minus Weekends and My Holidays

(OP)
This is in reference to thread702-1723375: Yearly calendar for attendance: Yearly calendar for attendance: 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.....

RE: Calculate Worked Days Minus Weekends and My Holidays

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.

CODE -->

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.

CODE

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 ]
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 

RE: Calculate Worked Days Minus Weekends and My Holidays

(OP)
MaJP, yes I still need this... I was patiently waiting bigsmile. 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.....

RE: Calculate Worked Days Minus Weekends and My Holidays

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.

CODE -->

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 

RE: Calculate Worked Days Minus Weekends and My Holidays

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

Thanks,
SoggyCashew.....

RE: Calculate Worked Days Minus Weekends and My Holidays

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

RE: Calculate Worked Days Minus Weekends and My Holidays

(OP)
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, empID)

Thanks,
SoggyCashew.....

RE: Calculate Worked Days Minus Weekends and My Holidays

CODE

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

CODE -->

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

RE: Calculate Worked Days Minus Weekends and My Holidays

(OP)
That worked perfect.... Thanks Again!

Thanks,
SoggyCashew.....

RE: Calculate Worked Days Minus Weekends and My Holidays

(OP)
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()

StartDate = DateSerial(Forms("frm_YearCalendar").cboYear, 1, 1)

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:

CODE --> sql

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.....

RE: Calculate Worked Days Minus Weekends and My Holidays

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

CODE -->

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.

CODE -->

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close