Excel Import VBA: Run-time 3075 Syntax error (missing operator) in query expression

Question

I am getting a missing operator error in the following SQL statement:

SELECT Sample.Number, Sample.SampleDate, BOD.BOD_Concentration_IN, BOD.BOD_Concentration_OUT, TSS.TSS_Influent, TSS.TSS_Effluent 
     FROM SampleInformation as Sample 
          INNER JOIN BOD_Data as BOD ON Sample.Number = BOD.Number 
          INNER JOIN TSS_Data as TSS ON Sample.Number = TSS.Number 
     WHERE (DATEPART('m',Sample.SampleDate) = DATEPART('m',#1/13/2016 12:01:00 PM#)) 
     AND (DATEPART('yyyy',Sample.SampleDate) = DATEPART('yyyy',#1/13/2016 12:01:00 PM#)) ORDER BY Sample.SampleDate

I eliminated the "WHERE" clause and still got the error, so it must be in the join.

Can anyone see what I am missing here? Thanks!

... and you could use the following: WHERE (MONTH(SampleDate) = MONTH(#1/13/2016 12:01:00 PM#)) AND (YEAR(SampleDate) = YEAR(#1/13/2016 12:01:00 PM#)) — Wayne G. Dunn
@WayneG.Dunn, your comment looks like the right answer. If you put it below (as an answer), then Mr. OReilly can mark this question "answered" and other SO users won't spend time working on this one. — tgolisch

Wayne G. Dunn Wayne G. Dunn · Accepted Answer · 2016-11-22T22:57:14

I finally figured out you are missing ( ) around the 'FROM' clause... try the following:

And BTW, 'Number' does not need to be enclosed in brackets (although it is a reserved word)

SELECT Sample.Number, Sample.SampleDate, BOD.BOD_Concentration_IN, BOD.BOD_Concentration_OUT, TSS.TSS_Influent, TSS.TSS_Effluent 
 FROM (SampleInformation AS Sample 
       INNER JOIN BOD_Data AS BOD ON Sample.Number = BOD.Number) 
       INNER JOIN TSS_Data AS TSS ON Sample.Number = TSS.Number
 WHERE (DATEPART('m',Sample.SampleDate) = DATEPART('m',#1/13/2016 12:01:00 PM#)) 
 AND (DATEPART('yyyy',Sample.SampleDate) = DATEPART('yyyy',#1/13/2016 12:01:00 PM#)) ORDER BY Sample.SampleDate

Excel Import VBA: Run-time 3075 Syntax error (missing operator) in query expression

1 Answers