Results 1 to 5 of 5

Thread: SQL syntax

  1. #1
    Join Date
    Jun 2006
    Posts
    48

    Unanswered: SQL syntax

    Hi there,
    I am getting this error on execution of a piece of code:

    The select statement includes a reserved word or an argument that is misspelled or missing or the punctuation is incorrect.


    Here is the sql:

    strSQL = "SELECT tblEvaluation.EvaluatorMgr, Count(tblEvaluation.EvaluatorMgr) AS MyCount, " & _
    "tblEvaluation.ECycle , tblEvaluation.Month, tblEvaluation.Year, " & _
    "FROM tblEvaluation " & _
    "WHERE (((tblEvaluation.EvaluatorMgr)=[txtRandomMgr]));"

    DoCmd.RunSQL (strSQL)
    If MyCount >= 2 Then
    MsgBox "This person already evaluated a member"
    End If

    Thanks for your assistance

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    have you viewed the SQL being sent to the sql parser, either set a break point after assigning the value to strSQL or do msgbox strsql

    on the face of it the SQL looks OK

    Buit I've a sneaking suspiscion that you need to rewrite it as you cannot use an aggregation with other columns unless they aredefined in a having clause

    if you are not using the other columns Id be tempted to rewrite the SQL so that

    Code:
    strSQL = "SELECT Count(tblEvaluation.EvaluatorMgr) AS MyCount" &_
    " FROM tblEvaluation" & _
    " WHERE tblEvaluation.EvaluatorMgr=[txtRandomMgr];"

  3. #3
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    For starters, you've got a comma between the last field in the SELECT clause and the word FROM, which is likely the cause of the error. Secondly, you can't use RunSQL for a SELECT query, only action queries. You'll need to open a recordset or switch to DCount. Thirdly, presuming [txtRandomMgr] is a form control, it needs to be concatenated in:

    "WHERE (((tblEvaluation.EvaluatorMgr)=" & [txtRandomMgr] & "));"

    presuming it's a numeric field. Add single quotes if it's text:

    "WHERE (((tblEvaluation.EvaluatorMgr)='" & [txtRandomMgr] & "'));"
    Paul

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Agreed with pbaldy... and Lollers at repeating the Access mistake of (((including) unnecessary brackets))!

    You can use "WHERE tblEvaluation.EvaluatorMgr=" & [txtRandomMgr] just as effectively.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    on secoind glance you may need to encapsulate the [txtRandomMgr] with either single or double quotes IF that field/colu,n is a text/string value.....

    eg
    & chr(34) & [txtRandomMgr] & chr(34)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •