I'm familiar with some queries but the previous programmer knew a lot more than me. The query is for a calendar we use in an Access database to schedule vacation for the employees in our department. The issue is when the report is run for a certain month, if the last week of the month ends in the next month, it doesn't show on the current month. For example, in August 2022 the last week ending date is 9/3/2022, but individuals who are off the entire week don't show on the August calendar and only show when the September calendar is run. I feel like it's got to be something in the query that has to do with the month but the query is very extensive. I'll post the first part of the query below, after that it just repeats for each calendar day so it would be the same thing. I think it's something that starts after the Union so I've bolded that part. Any help would be very appreciated!
SELECT Left([FirstName],1) & [LastName] & IIf([tbAttendance_Schedule].[TypeID]=1," - S",IIf([tbAttendance_Schedule].[TypeID]=4," - DD",IIf([tbAttendance_Schedule].[TypeID]=8," - F",IIf([tbAttendance_Schedule].[TypeID]=9," - JD",IIf([tbAttendance_Schedule].[TypeID]=10," - FMLA",IIf([tbAttendance_Schedule].[TypeID]=11," - DIS",IIf([tbAttendance_Schedule].[TypeID]=13," - ML",IIf([tbAttendance_Schedule].[TypeID]=5," - " & [Hours] & " DH","N/A")))))))) AS EMAID, IIf([tbAttendance_Schedule.PlannedFlag]=True And [tbAttendance_Schedule.TypeID]>13,"Planned Time Off",IIf([tbAttendance_Schedule.PlannedFlag]=False And [tbAttendance_Schedule.TypeID]>13,"Unplanned Time","N/A")) AS RG, Day(tbAttendance_Dates.Date) AS [Day], GetWeekEndingActual([tbAttendance_Dates.Date]) AS [W/E], Year(tbAttendance_Dates.Date) AS [Year], Month(tbAttendance_Dates.Date) AS [Month], [MonthName] & " " & [Year] AS MY FROM (tbUser INNER JOIN tbUser_Skill ON tbUser.SkillID = tbUser_Skill.SkillID) INNER JOIN (((tbAttendance_Dates INNER JOIN tbDates ON tbAttendance_Dates.Date = tbDates.Date) INNER JOIN (tbAttendance_Schedule LEFT JOIN tbAttendance_Hours ON tbAttendance_Schedule.SchedID = tbAttendance_Hours.SchedID) ON tbAttendance_Dates.VacID = tbAttendance_Schedule.VacID) LEFT JOIN tbAttendance_Reason ON tbAttendance_Schedule.SchedID = tbAttendance_Reason.SchedID) ON tbUser.OLMSID = tbAttendance_Schedule.OLMSID WHERE (((tbAttendance_Schedule.TypeID) In (1,4,5,8,9,10,11,13)) AND ((tbAttendance_Schedule.DeletedFlag)=False) AND ((tbUser.ActiveFlag)=True)); Union SELECT Left([FirstName],1) & [LastName] & " - VW" AS EMAID, "" AS RG, Day(tbAttendance_Dates.Date-5) AS [Day], GetWeekEndingActual([tbAttendance_Dates.Date]) AS [W/E], Year(tbAttendance_Dates.Date) AS [Year], Month(tbAttendance_Dates.Date) AS [Month], [MonthName] & " " & [Year] AS MY FROM (tbUser INNER JOIN tbUser_Skill ON tbUser.SkillID = tbUser_Skill.SkillID) INNER JOIN (((tbAttendance_Dates INNER JOIN tbDates ON tbAttendance_Dates.Date = tbDates.Date) INNER JOIN (tbAttendance_Schedule LEFT JOIN tbAttendance_Hours ON tbAttendance_Schedule.SchedID = tbAttendance_Hours.SchedID) ON tbAttendance_Dates.VacID = tbAttendance_Schedule.VacID) LEFT JOIN tbAttendance_Reason ON tbAttendance_Schedule.SchedID = tbAttendance_Reason.SchedID) ON tbUser.OLMSID = tbAttendance_Schedule.OLMSID WHERE (((tbAttendance_Schedule.TypeID) In (2)) AND ((tbAttendance_Schedule.DeletedFlag)=False) AND ((tbUser.ActiveFlag)=True));