This one trips people up all the time.
Your inner query (
only) should show the notes and be filtered to the date range.
When you have that working you can run a standard 'missing' style query by linking the results of the inner query as a LEFT JOIN to the Client table and filtering for Null in the linked field(s) of the inner query.
So, say the inner query is named qryClientNote and returns two fields to make it unique - [ClientID] & [NoteDate] - and the Client table also has a [ClientID] field to identify Client records, then the outer query might look like (in SQL) :
- SELECT tC.*
-
FROM [tblClient] AS tC
-
LEFT JOIN
-
[qryClientNote] AS qCN
-
ON tC.ClientID=qCN.ClientID
-
WHERE (qCN.NoteDate Is Null)