Subquery to use date as Criteria (1 Viewer)

[bgmiller]

I have a tblPaychecks with all the paychecks for each perioddate
I have a tblCompensation with compensation records for each employee. Both can connect on the HireID
The tblCompensation has multiple records for each HireID with effectivedates. I want to lookup the latest compensation record for each perioddate.

So far I have this SQL:

SELECT tblPaycheck.* FROM tblCompensation RIGHT JOIN tblPaycheck ON tblCompensation.HireID = tblPaycheck.HireID
WHERE tblCompensation.EffectiveDate=(SELECT Max(EffectiveDate) FROM tblCompensation WHERE tblCompensation.HireID =tblPaycheck.HireID AND tblCompensation.effectiveDate <= #(tblPaycheck.PeriodDate)#);

The problem is with the last line: tblCompensation.effectiveDate <= #(tblPaycheck.PeriodDate)#);
I get a syntax error. Although When I enter a date value in for tblPaycheck.PeriodDate to test it, everything works fine

How do I reference another field in the query that is a date?

Thanks
Brian

 

[Jon K]

Assuming both effectiveDate and PeriodDate are date fields, the last line is:-

tblCompensation.effectiveDate <= tblPaycheck.PeriodDate;

 

[bgmiller]

Duh!

But that's too easy, I'd rather make it complicated..
I guess thats what I get when I think too much
Thanks