What is the syntax in MS Access VB for NOT Exists

[jmccormack]

I am tring to write a MS Access VB code to pull records from a table Consultant where a record does not exist in the table Reviews with the following criteria
Consultant.Id = Reviews.Consult antId
Reviews.ReviewT ype = Year End
Reviews.ReviewY ear = 2010
Consultant.Stat us = Active

The SQL below works as part of a query

SELECT CONSULTANT.ID, CONSULTANT.LastName, CONSULTANT.Status
FROM CONSULTANT
WHERE (((CONSULTANT.Status)="Active") AND ((NOT Exists (SELECT * FROM REVIEWS
WHERE CONSULTANT.ID = REVIEWS.ConsultantId 
AND REVIEWS.ReviewType = "Year End" 
AND REVIEWS.ReviewYear = 2010))));

However, I need to call this from my VB code so i can create Reviews for the missing records.

[NeoPa]

I expect the proper way in any SQL would be to use a LEFT JOIN in the FROM clause and say ON Reviews.Consult antID IS NULL.

I'm not sure where VBA even comes into this question, but obviously, were you to need to create your SQL string in VBA before applying it then you would similarly have to write it to produce such SQL.