Hello! sorry for the wording but I'm finding my problem hard to describe in title format.
I have a query which is used for a report that shows vacation time taken during a certain year.
It does its job well, but I don't get data for employees who have not taken vacation this year (or ever).
ok, easy fix right? just make it a right join from employees and slap an Or Is Null on the part that's looking at the year of each vacation entry.
except when an employee has taken time off in previous years, they aren't null. their entries just fail the criteria, so they don't show up on the report.
I was trying to think of a way to use IIF on the query but I couldn't come up with anything. I hope this isn't just a brain fart.
Query SQL:
Code:
SELECT Employees.ID, Employees.First_Name, Employees.Last_Name, Vacation_Master.Calendar_Year, Vacation_Master.Total_Vacation_Days, Vacation_Time.Start_Date, Vacation_Time.End_Date, Vacation_Time.Vacation_Days_UsedFROM Vacation_Master INNER JOIN (Vacation_Time RIGHT JOIN Employees ON Vacation_Time.Employee_ID = Employees.ID) ON Vacation_Master.Employee_ID = Employees.ID
WHERE (((Vacation_Master.Calendar_Year)=[Forms]![MainForm]![vac_Days_Calc_Year]) AND ((Year([Vacation_Time].[Start_Date]))=[Forms]![MainForm]![vac_Days_Calc_Year] Or (Year([Vacation_Time].[Start_Date])) Is Null) AND ((Employees.Sort)<>0));
You can find some example data in the attached image. note employee 4 will not show up in a query for year 2020.