469,097 Members | 1,244 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,097 developers. It's quick & easy.

What is the syntax in MS Access VB for NOT Exists

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

Expand|Select|Wrap|Line Numbers
  1. SELECT CONSULTANT.ID, CONSULTANT.LastName, CONSULTANT.Status
  2. FROM CONSULTANT
  3. WHERE (((CONSULTANT.Status)="Active") AND ((NOT Exists (SELECT * FROM REVIEWS
  4. WHERE CONSULTANT.ID = REVIEWS.ConsultantId 
  5. AND REVIEWS.ReviewType = "Year End" 
  6. AND REVIEWS.ReviewYear = 2010))));
However, I need to call this from my VB code so i can create Reviews for the missing records.
Nov 16 '10 #1
1 2252
NeoPa
32,159 Expert Mod 16PB
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.
Nov 17 '10 #2

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by Jarrod Morrison | last post: by
1 post views Thread by Just_Buy | last post: by
8 posts views Thread by Paul E Collins | last post: by
14 posts views Thread by clarence_rollins | last post: by
7 posts views Thread by Stephen Martinelli | last post: by
reply views Thread by kesz1 | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by kglaser89 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.