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
– Wayne G. Dunn, Commented Nov 22, 2016 at 20:02
@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
– tgolisch, Commented Nov 22, 2016 at 22:25

Wayne G. Dunn · Accepted Answer · 2016-11-23 19:02:27Z

0

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

edited Nov 23, 2016 at 19:02

answered Nov 22, 2016 at 22:57

Wayne G. Dunn

4,3322 gold badges15 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

PhillipOReilly Over a year ago

Wayne, you were close (see edits in your answer), and your original post got me on the right track. Thanks!

Wayne G. Dunn Over a year ago

Yes, you did exactly what I did ... used the query builder to create the proper syntax, then add criteria. As for your edit, I rejected it because the only difference you have now is that you enclosed some of the field names in brackets [].... which I also determined didn't cause the issue, so I didn't use them. Glad it's working!!

PhillipOReilly Over a year ago

Okay, fair enough! Thanks for your input.

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related