Why won't this query work?

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.

SHUREINCAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jaime OlivaresSoftware ArchitectCommented:
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)

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jaime OlivaresSoftware ArchitectCommented:
It is always useful to display your entire query string (strSQL1 in this case) in a messagebox to review it before query is done.
0
EDDYKTCommented:
>>"SELECT qrymasterscenerios.Number," & _

same as this one
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Jaime OlivaresSoftware ArchitectCommented:
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"

0
JR2003Commented:
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.
JR
0
inthedarkCommented:
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," & _

to

 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.
0
SHUREINCAuthor Commented:
Thank you all for your support.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.