Unmatched Query issue

[heathramos2]

I am trying to create a simple unmatched query but I am having issues.

Situation:

Query one contains employee number and hired date
Query two contains employee number and termination date

I want to prompt for a date and return the employees that were hired before that date and weren't terminated.

I created a form that has a text box where people can enter a date and both query one and two reference it. Query one returns records <= Date and query two returns <= Date.

In plain english, return everyone that was hired by a certain date and minus out the employees that were terminated before that date.

When I ran the unmatched query, it came back with no records. If I change query two and hard code a date in it, the unmatched query returns 281 records.

not sure why.

 

[PHV]

What have you tried so far and where in your SQL code are you stuck ?
Have a look here:

http://www.devshed.com/c/a/MySQL/Understanding-SQL-Joins

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[heathramos2]

Query One:
SELECT dbo_Employees.SystemAssignedPersonID, dbo_Employees.CurrentHireDate
FROM dbo_Employees
WHERE (((dbo_Employees.CurrentHireDate)<=[Forms]![Head Count Form]![HCDate]))
ORDER BY dbo_Employees.CurrentHireDate DESC;
Query Two:
SELECT dbo_EmployeesTerminationInfo.SystemAssignedPersonID, dbo_EmployeesTerminationInfo.TerminationDate
FROM dbo_EmployeesTerminationInfo
WHERE (((dbo_EmployeesTerminationInfo.TerminationDate)<=[Forms]![Head Count Form]![HCDate]));
Unmatched Query:
SELECT [Head Count Historical Hire Date].SystemAssignedPersonID, [Head Count Historical Hire Date].CurrentHireDate, [Head Count Historical Term Date].TerminationDate
FROM [Head Count Historical Hire Date] LEFT JOIN [Head Count Historical Term Date] ON [Head Count Historical Hire Date].SystemAssignedPersonID = [Head Count Historical Term Date].SystemAssignedPersonID
WHERE ((([Head Count Historical Term Date].TerminationDate) Is Null));

 

[heathramos2]

If I run this with a date such as 02/02/08, it doesn't return any records.

If I change Query two to <=#02/02/08# and run the unmatched query, it works.

If I change the queries to prompt instead of referencing a form, it still doesn't work.

If I change the the unmatched query and get rid of the IS Null portion, it returns only the records from query two.