Using Minus operator

[aman]

The HeadingID,HeadingName are stored in lkupHeadings.

The HeadingID,Goals,Measures,Weighting,Staffnumber,DateFrom,DateTo etc are stored in lkupObjectives.

Now I am writing the following code;

 Dim strsql As String
strsql = "(select Headingid from lkupHeadings) minus (select HeadingID from lkupobjectives where [StaffNumber]='" & Environ("Username") & "')"
 MsgBox strsql
 CboHeading.RowSource = strsql

This gives me "Syntex error in Union query".

Any help will be much appreciated

Thanks

 

[Grumm]

You can try this :
strsql = "select HeadingID from lkupHeadings where NOT EXISTS (select HeadingID from lkupobjectives where lkupobjectives.HeadingID = lkupHeadings.HeadingID [StaffNumber]='" & Environ("Username") & "'"

Or NOT IN or just a plain old left join.

In any case, Access doesn't support MINUS...

 

[arnelgp]

Or:

Dim strsql As String
strsql = "(select Headingid from lkupHeadings) Where HeadingID Not In (select HeadingID from lkupobjectives where [StaffNumber]='" & Environ("Username") & "')"
MsgBox strsql
CboHeading.RowSource = strsql