[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now


Why won't this query work?

Posted on 2004-10-22
Medium Priority
Last Modified: 2010-05-02
Hello All

When I try to run this query I am getting the following message:
The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect.

Private Sub cmdReport_Click()
Dim oldDbName As String
Dim wspDefault As Workspace
Dim dbsMikesDatabase As Database
Dim strSQL1 As String
Dim rstFromQuery1 As recordset

'Set the path to the database
oldDbName = "L:\Mfg Engineering\Mike\ProdHoldDbase\ProductionHoldvers1.mdb"
'Create a default workspace Object
Set wspDefault = DBEngine.Workspaces(0)

'Create a database object.
Set dbsMikesDatabase = wspDefault.OpenDatabase(oldDbName)

'The SQL statement
strSQL1 = "SELECT qrymasterscenerios.Number," & _
"FROM qrymasterscenerios," & _
"ORDER BY qrymasterscenerios.Number DESC"
'strSQL = "SELECT qrymasterscenerios.ProdholdID, qrymasterscenerios.Number, qrymasterscenerios.MainContact"
'"qrymasterscenerios.Dateopened, qrymasterscenerios.DateReleased, qrymasterscenerios.Product, qrymasterscenerios.scenario," & _
'"qrymasterscenerios.Department, qrymasterscenerios.Reason, qrymasterscenerios.RootCause, qrymasterscenerios.NMRnbr," & _
'"qrymasterscenerios.[8Dnbr], qrymasterscenerios.Comments," & _
'"FROM qrymasterscenerios"
'"WHERE (((qrymasterscenerios.Dateopened) Between [forms]![frmqrydtprodscendept]![beginningdate] And [forms]![frmqrydtprodscendept]![endingdate])" & _
'"AND ((qrymasterscenerios.Product) Like [forms]![frmqrydtprodscendept]![product])" & _
'"AND ((qrymasterscenerios.scenario) Like [forms]![frmqrydtprodscendept]![scenario])" & _
'"AND ((qrymasterscenerios.Department) Like [forms]![frmqrydtprodscendept]![department]));"

'Create a Snapshot Type Recordset from the SQL query
Set rstFromQuery1 = dbsMikesDatabase.OpenRecordset(strSQL1, dbOpenSnapshot)

End Sub

Even when I break it down to:
'The SQL statement
strSQL1 = "SELECT qrymasterscenerios.Number," & _
"FROM qrymasterscenerios," & _
"ORDER BY qrymasterscenerios.Number DESC"
'Create a Snapshot Type Recordset from the SQL query
Set rstFromQuery1 = dbsMikesDatabase.OpenRecordset(strSQL1, dbOpenSnapshot)

End Sub

I am still getting the error.
Any help would be greatly appreciated.

Question by:SHUREINC
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
LVL 55

Accepted Solution

Jaime Olivares earned 1000 total points
ID: 12382577
Found a bug, I guess:

strSQL1 = "SELECT qrymasterscenerios.Number," & _
"FROM qrymasterscenerios," & _                           <---- this comma must be a space instead
"ORDER BY qrymasterscenerios.Number DESC"
'Create a Snapshot Type Recordset from the SQL query
Set rstFromQuery1 = dbsMikesDatabase.OpenRecordset(strSQL1, dbOpenSnapshot)

LVL 55

Expert Comment

by:Jaime Olivares
ID: 12382603
It is always useful to display your entire query string (strSQL1 in this case) in a messagebox to review it before query is done.
LVL 26

Expert Comment

ID: 12382726
>>"SELECT qrymasterscenerios.Number," & _

same as this one
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!

LVL 55

Expert Comment

by:Jaime Olivares
ID: 12382801
Resumming. This must be your string

strSQL1 = "SELECT qrymasterscenerios.Number FROM qrymasterscenerios ORDER BY qrymasterscenerios.Number DESC"

instead of:

strSQL1 = "SELECT qrymasterscenerios.Number, FROM qrymasterscenerios, ORDER BY qrymasterscenerios.Number DESC"

LVL 18

Expert Comment

ID: 12384282
I agree with eveyone else.  Seems to be a comma where there shouuldn't be!
So the program that interprates the query thinks you want to select a column called "FROM" and that you want to give it an alias of "qrymasterscenerios". "FROM is obviously a reserved word and one that you can't give to a column on a table.
LVL 17

Assisted Solution

inthedark earned 1000 total points
ID: 12387751
I agree with the comma but also have another comment...this query would work on sql server but not with Access becuase of the reserved word you have used in a field name. Change:

qrymasterscenerios.Number," & _


 qrymasterscenerios.[Number]," & _

Another common probelm is found when you have a field called size.  Size & Number both cause Access to throw an unspecified error.  Somehow SQL server is able to cope with a column called Number, but Access can't.

Some people think that you should future-proof your code.  Sometimes it is best to always use ilegal field names, so that the [] braces always surround your fiedl names.  For example, all fields in the table qrymasterscenerios should be prefixed with say "QMS " so you get fields [QMS Number], [QMS Department].  Future relases of Access/SQL server may introduce new reserved words like Senario or Department for example.  This ploy adds a little more effort to your coding but solves the problems you get when two fields in different tables have then same name.

Author Comment

ID: 12594081
Thank you all for your support.

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses

650 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