Solved

Why won't this query work?

Posted on 2004-10-22
159 Views
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.

0
Question by:SHUREINC
    7 Comments
     
    LVL 55

    Accepted Solution

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

    Expert Comment

    by:Jaime Olivares
    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
     
    LVL 26

    Expert Comment

    by:EDDYKT
    >>"SELECT qrymasterscenerios.Number," & _

    same as this one
    0
     
    LVL 55

    Expert Comment

    by:Jaime Olivares
    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
     
    LVL 18

    Expert Comment

    by: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
    0
     
    LVL 17

    Assisted Solution

    by:inthedark
    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
     

    Author Comment

    by:SHUREINC
    Thank you all for your support.
    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Introduction This article makes the case for using two modules in your VBA/VB6 applications to provide both case-sensitive and case-insensitive text comparison operations.  Recently, I solved an EE question using the LIKE function.  In order for th…
    Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
    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…
    Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

    931 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

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now