Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Syntax error (missing operator) SQL

Posted on 2010-11-17
4
294 Views
Last Modified: 2012-05-10
Experts,

I'm trying run the SQL below in the on open event, and set it equal to a graphs's row source. I copied and pasted it from a query that works, but I'm getting the attached syntax error (missing operator)  when I try to run it from the on open event.

Thanks,
James
strSQL2 = "SELECT (Format([MDate],'mmm'' '''yy')) AS Expr1, Avg(qryMAIN_Patient_WtHtBMI_calc3.BMI4A_P) AS AvgOfBMI4A_P " & _
"FROM qryMAIN_Patient_WtHtBMI_calc3 " & _
"GROUP BY(Format([MDate], 'mmm'' '''yy')), (Year([MDate]) * 12 + Month([MDate]) - 1) " & _
"ORDER BY (Year([MDate])*12+Month([MDate])-1)"

Open in new window

error.PNG
0
Comment
Question by:jdallain
  • 2
4 Comments
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 34155475
Try this code

strSQL2 = "SELECT (Format([MDate],'''mmm''' '''yy''')) AS Expr1, Avg(qryMAIN_Patient_WtHtBMI_calc3.BMI4A_P) AS AvgOfBMI4A_P " & _
"FROM qryMAIN_Patient_WtHtBMI_calc3 " & _
"GROUP BY(Format([MDate], '''mmm''' '''yy''')), (Year([MDate]) * 12 + Month([MDate]) - 1) " & _
"ORDER BY (Year([MDate])*12+Month([MDate])-1)"

Open in new window


Need more apostrophe

Raj
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 34155484
what about this:

strSQL2 = "SELECT (Format([MDate],'mmm yy')) AS Expr1
, Avg(qryMAIN_Patient_WtHtBMI_calc3.BMI4A_P) AS AvgOfBMI4A_P " & _
"FROM qryMAIN_Patient_WtHtBMI_calc3 " & _
"GROUP BY(Format([MDate], 'mmm yy')), (Year([MDate]) * 12 + Month([MDate]) - 1) " & _
"ORDER BY (Year([MDate])*12+Month([MDate])-1)"

Open in new window

0
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 34155513
Oh! I think, angelIII provided right solution

Raj
0
 

Author Closing Comment

by:jdallain
ID: 34155565
Thanks  a lot fellows!

James
0

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question