Vb.net Sql Query Not Working In Vb But Does In Access

I'm trying to work out why this query does not return results. The same query works fine in Access but not in VB.Net. Any help would be appreciated. The code Fails when trying to f

Solution 1:

There is no way that works in MS Access either. You would have to do something like:

SELECT*FROM Forecast_TDL 
WHERE Forecast_TDL.EIAC NOTIN(SELECT EIAC FROM Grouped_Servicings) AND 
Forecast_TDL.LCN NOTIN(SELECT LCN FROM Grouped_Servicings) AND
Forecast_TDL.Servicing NOTIN(SELECT Servicing FROM Grouped_Servicings) And 
Forecast_TDL.Interval NOTIN(SELECTIntervalFROM Grouped_Servicings) And 
Forecast_TDL.Interval_Type NOTIN (SELECT Interval_Type FROM Grouped_Servicings)

After understanding the question better, you can avoid comparing concatenated strings by using a JOIN and finding where a row doesn't exist.

SELECT*FROM Forecast_TDL
LEFTOUTERJOIN Grouped_Servicings
ON Forecast_TDL.EIAC = Grouped_Servicings.EIAC AND
 Forecast_TDL.LCN = Grouped_Servicings.LCN AND
 Forecast_TDL.Servicing = Grouped_Servicings.Servicing AND
 Forecast_TDL.Interval = Grouped_Servicings.Interval AND
 Forecast_TDL.Interval_Type = Grouped_Servicings.Interval_Type
WHERE Grouped_Servicings.EIAC ISNULLOR
      Grouped_Servicing.LCN ISNULLOR
      Grouped_Servicing.Servicing ISNULLOR
      Grouped_Servicing.Interval ISNULLOR
      Grouped_Servicing.Interval_Type ISNULL

Solution 2:

From my point of view you need spaces before the clauses like "Where" and "Not in". And remove the semicolone at end of the query.