0

I get the

"Syntax error (missing operator) in query expression..."

When running this query from one Excel spreadsheet to another using Provider=Microsoft.ACE.OLEDB.12.0

select Distinct [set$].F1 as SETID, [set$].F7 as SetVolume, [tray$].F8 as fracwidth
from [set$]
Left join [traysinset$] as TS1 on [set$].F1=TS1.F2
Left join [traysinset$] as TS2 on TS1.F3=TS2.F2
Left join [tray$] on [tray$].F1=TS1.F3 or [tray$].F1=TS2.F3
where [set$].F1 <>'' and [set$].F1 is not Null and [tray$].F8 <>'' and [tray$].F8 is not Null and [tray$].F8 <>'NULL'
ORDER BY [set$].F1 ASC, [tray$].F8 ASC

I know the issue is my joins and have tried various bracketing and nesting but can't seem to crack it. I have successfully run the query in SQL server management studio as the below so I'm really stumped as to why it doesn't work in Excel.

select Distinct [set].id as SETID, [set].volume as SetVolume, [tray].fracwidth as fracwidth
from [myserver].[dbo].[set]
Left join [myserver].[dbo].[traysinset] as TS1 on [set].id=TS1.setid
Left join [myserver].[dbo].[traysinset] as TS2 on TS1.trayid=TS2.setid
Left join [myserver].[dbo].[tray] on [tray].id=TS1.trayid or [tray].id=TS2.trayid
where [set].id <>'' and [set].id is not Null and [tray].fracwidth <>'' and [tray].fracwidth is not Null and [tray].fracwidth <>'NULL'
ORDER BY [set].id ASC, [tray].fracwidth ASC

All help much appreciated.

June7
  • 19,874
  • 8
  • 24
  • 34
Richard
  • 1
  • 1
    Not sure about the solution but do you mean to do this comparrison because this is looking for 'NULL' as a text string, not a NULL value: [tray$].F8 <>'NULL' – Brad May 13 '21 at 16:17
  • 1
    If it follows syntax same as Access SQL, probably needs nested parentheses around JOIN clauses, something like. `FROM ((T1 LEFT JOIN t2 ON t1.field = t2.field) LEFT JOIN t3 on t2.field = t3.field) LEFT JOIN t4 ON t3.field = t4.field`. https://stackoverflow.com/questions/19367565/access-sql-inner-join-with-multiple-tables. I would use Access query builder to get correct syntax then port that over to Excel VBA. – June7 May 13 '21 at 18:24
  • 1
    Does this answer your question? [Access-SQL: Inner Join with multiple tables](https://stackoverflow.com/questions/19367565/access-sql-inner-join-with-multiple-tables) – June7 May 13 '21 at 18:26
  • Where are you stuck justifying that what you wrote agrees with the syntax in the manual? If you can't justify it, why did you write it? (Rhetorical.) PS When code has errors, chop it down until it doesn't. – philipxy May 13 '21 at 23:06
  • Thanks June7 that helped alot. With the correct bracketing I have narrowed down the issue to my OR statement select Distinct [set$].F1 as SETID, [set$].F7 as SetVolume, [tray$].F8 as fracwidth from (([set$] Left join [traysinset$] as TS1 on [set$].F1=TS1.F2) Left join [traysinset$] as TS2 on TS1.F3=TS2.F2) Left join [tray$] on [tray$].F1=TS1.F3 where [set$].F1 <>'' and [set$].F1 is not Null and [tray$].F8 <>'' and [tray$].F8 is not Null and [tray$].F8 <>'NULL' ORDER BY [set$].F1 ASC, [tray$].F8 ASC This works but I need my last join to be [tray$].F1=TS1.F3 or [tray$].F1=TS2.F3 – Richard May 15 '21 at 17:28
  • Brad, my [tray$].F8 <>'NULL' code is because the cells in excel can't actually be Null they can only be empty string '' or some have the actual text 'NULL' so technically my [tray$].F8 is not Null statement is redundant but the afforementioned code is necessary. Either way I keep both in when writing code to make sure I catch all – Richard May 15 '21 at 17:35

0 Answers0