SQL subquery using DLookup

[WVZ]

Hi

I am having trouble with inserting another qualifier in the SQL statement I have outlined below. The aim is to find all records where an employee has not yet

completed a time sheet. To do that I first run a query to find non-matches against a set calendar, with the provision to exclude public holidays and weekends. So

I am first doing a loop within each employee to find the missing dates. However, the portion that I cannot manage, is to do a DLookup where the outstanding time

sheet information is greater than the Employment Date of this particular employee. Normally this would be

DLookup("[EmploymentDate]","tblEmplyees","[EmployeeID="& {Current Loop}) The variable {Current Loop} is where the problem lies. I need each loop to look at the

individual employment date of this person, represented by the Debug.Print ("One " &rsl![EmployeeId]).

How can I mimic a DLookup inside the SQL statement for each record?

Any help will be most welcome.

Regards

Werner




Sub EmailIt()
On Error GoTo Err1

Dim db As Database
Dim rs As Recordset
Dim rs1 As Recordset
Dim strSQL As String
Dim strSQLsub As String

strSQLsub = "SELECT [EmployeeID] FROM [tbl_Emp_Temp]"

strSQL = "SELECT tblTSHeader.EmployeeID, tblTSHeader.Date, [tblDate].Date, [tblDate].Description, [tblEmployees].EmploymentDate, Weekday([tblDate].Date) AS

[Day]"
strSQL = strSQL & "FROM tblDate LEFT JOIN _One ON [tblDate].Date = [_One].Date "
strSQL = strSQL & &quot;WHERE ((([tblDate].Date) < Now()) And (([_One].Date) Is Null) And (([tblDate].Description) Is Null) And ((Weekday([tblDate].Date)) <> 1 And

(Weekday([tblDate].Date)) <> 7)&quot;

Set db = CurrentDb()
Set rs1 = db.OpenRecordset(strSQLsub)

Do While Not rs1.EOF
Debug.Print (&quot;One &quot; & rs1![EmployeeID])

Set rs = db.OpenRecordset(strSQL)

Do While Not rs.EOF
Debug.Print (rs![tblDate.Date])
rs.MoveNext
Loop

rs.Close

rs1.MoveNext
Loop

rs1.Close
db.Close


Exit1:
Exit Sub

Err1:
MsgBox Err.Number & &quot; &quot; & Err.Description, vbInformation, &quot;emailing error...&quot;
Resume Exit1
End Sub