I am trying to write a query which finds where records dont exist between to sets of week numbers. My Access query looks like this at the moment:
SELECT PEPERSON.PERSONNEL_NO, PEPERSON.SURNAME, PEPERSON.CENTRE, [Input Week Number] AS WEEK_NO
FROM PEPERSON
WHERE (((PEPERSON.CENTRE)>"0") AND ((Exists (SELECT JCBATCH.PERSONNEL_NO FROM JCBATCH WHERE JCBATCH.COMPANY = PEPERSON.COMPANY AND JCBATCH.WEEK_NO = [Input Week Number] AND JCBATCH.PERSONNEL_NO = PEPERSON.PERSONNEL_NO))=False) AND ((PEPERSON.DATE_LEFT) Is Null) AND ((PEPERSON.USE_IN_TB)=1) AND ((PEPERSON.COMPANY)="PFC1"))
ORDER BY PEPERSON.PERSONNEL_NO;
This works fine when I enter in the [Input Week Number] but I need to replace with this something like:
Between "201001" and "201052" which also displays the result of the week number in the field called WEEK_NO.
Many thanks for your time to look at my post and any help would be most appreciated.