query criteria with exceptions

[Indigobuni]

I have a db of employee information and need help with a query.

My table has all the employee information, including hire date, terminated date, and rehire date. I have one query of terminated employees, but need help with the active employees query.

There are a couple employees who were terminated, but rehired. I can't use "Is Null" in the terminated date field because that is filled in for them, so even though they were rehired, they wouldn't show up.

Is there an expression I can build that would allow for exceptions? I'm guessing there is, but can't think of it. Thanks.

 

[Beetle]

It probably would simplify your life a bit if you just add a boolean (Yes/No) field to the table to determine if the employee is active. Set to False if they are terminated and back to True if they are rehired. To solve your problem with your table as is, you can use a query like the following;

SELECT tblEmployees.EmpName, tblEmployees.HireDate, tblEmployees.TermDate, tblEmployees.RehireDate
FROM tblEmployees
WHERE tblEmployees.TermDate Is Null OR tblEmployees.RehireDate Is Not Null;