Results 1 to 4 of 4
  1. #1
    MrBologna is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Sep 2018
    Posts
    48

    Get a return of 0 or null when criteria is failed

    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.
    Attached Thumbnails Attached Thumbnails DataSnip.png  

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    Build query that applies year filter then join that query to Employees.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    patpat22 is offline Novice
    Windows 10 Office 365
    Join Date
    Jul 2020
    Posts
    1
    Quote Originally Posted by MrBologna View Post
    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.
    *bump*

    Do you need any help with it now? I will bump this thread just in case.

  4. #4
    MrBologna is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Sep 2018
    Posts
    48
    I actually just got around to trying this, last week was a little hectic.

    I was able to get what I wanted after separating the year filter like June mentioned.

    Thank you June for the help and thank you Pat for making sure I wasn't forgotten about!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 10
    Last Post: 01-24-2020, 06:33 PM
  2. Replies: 2
    Last Post: 10-07-2019, 10:46 AM
  3. Replies: 4
    Last Post: 04-09-2019, 02:05 PM
  4. Replies: 3
    Last Post: 07-11-2014, 08:24 AM
  5. Replies: 1
    Last Post: 02-23-2012, 02:27 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums