Solved

Syntax error (missing operator) SQL

Posted on 2010-11-17
4
296 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
[X]
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
  • 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

690 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