I am using an inner join on the following query, and trying to figure out how to join my 2 tables so that only the row from work_hist with the week_end date less than or equal to wage_hist.eff_date is returned. I keep getting syntax errors when I modify the work_hist.week_end into a subselect. Can anyone help? Below is my query.

SELECT wage_hist.ASC_SS_NBR,
wage_hist.ASC_STORE_NBR,
wage_hist.ASC_DIVISION_NBR,
wage_hist.EFF_DATE, wage_hist.STORE_JOB_CODE,
wage_hist.BENEFIT_HOURS,
IIf( IsNull(work_hist.WEEKS_WORKED),wage_hist.work_week s,wage_hist.work_weeks -work_hist.WEEKS_WORKED) AS benefit_weeks INTO WageHistoryWeeksWorked
FROM wage_hist LEFT JOIN work_hist ON (wage_hist.EFF_DATE <= work_hist.WEEK_END) AND (wage_hist.ASC_SS_NBR = work_hist.ASC_SSN_NBR);