Link to home
Start Free TrialLog in
Avatar of SHUREINC
SHUREINC

asked on

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.

ASKER CERTIFIED SOLUTION
Avatar of Jaime Olivares
Jaime Olivares
Flag of Peru image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
It is always useful to display your entire query string (strSQL1 in this case) in a messagebox to review it before query is done.
>>"SELECT qrymasterscenerios.Number," & _

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

Avatar of JR2003
JR2003

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
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of SHUREINC

ASKER

Thank you all for your support.