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\ProdHoldD base\Produ ctionHoldv ers1.mdb"
'Create a default workspace Object
Set wspDefault = DBEngine.Workspaces(0)
'Create a database object.
Set dbsMikesDatabase = wspDefault.OpenDatabase(ol dDbName)
'The SQL statement
strSQL1 = "SELECT qrymasterscenerios.Number, " & _
"FROM qrymasterscenerios," & _
"ORDER BY qrymasterscenerios.Number DESC"
'strSQL = "SELECT qrymasterscenerios.Prodhol dID, qrymasterscenerios.Number, qrymasterscenerios.MainCon tact"
'"qrymasterscenerios.Dateo pened, qrymasterscenerios.DateRel eased, qrymasterscenerios.Product , qrymasterscenerios.scenari o," & _
'"qrymasterscenerios.Depar tment, qrymasterscenerios.Reason, qrymasterscenerios.RootCau se, qrymasterscenerios.NMRnbr, " & _
'"qrymasterscenerios.[8Dnb r], qrymasterscenerios.Comment s," & _
'"FROM qrymasterscenerios"
'"WHERE (((qrymasterscenerios.Date opened) Between [forms]![frmqrydtprodscend ept]![begi nningdate] And [forms]![frmqrydtprodscend ept]![endi ngdate])" & _
'"AND ((qrymasterscenerios.Produ ct) Like [forms]![frmqrydtprodscend ept]![prod uct])" & _
'"AND ((qrymasterscenerios.scena rio) Like [forms]![frmqrydtprodscend ept]![scen ario])" & _
'"AND ((qrymasterscenerios.Depar tment) Like [forms]![frmqrydtprodscend ept]![depa rtment])); "
'Create a Snapshot Type Recordset from the SQL query
Set rstFromQuery1 = dbsMikesDatabase.OpenRecor dset(strSQ L1, 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.OpenRecor dset(strSQ L1, dbOpenSnapshot)
End Sub
I am still getting the error.
Any help would be greatly appreciated.
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\ProdHoldD
'Create a default workspace Object
Set wspDefault = DBEngine.Workspaces(0)
'Create a database object.
Set dbsMikesDatabase = wspDefault.OpenDatabase(ol
'The SQL statement
strSQL1 = "SELECT qrymasterscenerios.Number,
"FROM qrymasterscenerios," & _
"ORDER BY qrymasterscenerios.Number DESC"
'strSQL = "SELECT qrymasterscenerios.Prodhol
'"qrymasterscenerios.Dateo
'"qrymasterscenerios.Depar
'"qrymasterscenerios.[8Dnb
'"FROM qrymasterscenerios"
'"WHERE (((qrymasterscenerios.Date
'"AND ((qrymasterscenerios.Produ
'"AND ((qrymasterscenerios.scena
'"AND ((qrymasterscenerios.Depar
'Create a Snapshot Type Recordset from the SQL query
Set rstFromQuery1 = dbsMikesDatabase.OpenRecor
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.OpenRecor
End Sub
I am still getting the error.
Any help would be greatly appreciated.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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"
strSQL1 = "SELECT qrymasterscenerios.Number FROM qrymasterscenerios ORDER BY qrymasterscenerios.Number DESC"
instead of:
strSQL1 = "SELECT qrymasterscenerios.Number,
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you all for your support.