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.ConsultantId
Reviews.ReviewType = Year End
Reviews.ReviewYear = 2010
Consultant.Status = 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.ConsultantID 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.