Results 1 to 5 of 5

Thread: SQL syntax

  1. #1
    Join Date
    Jun 2006

    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
    out on a limb
    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

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

  3. #3
    Join Date
    May 2005
    Nevada, USA
    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] & "'));"

  4. #4
    Join Date
    Nov 2007
    Adelaide, South Australia
    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
    out on a limb
    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.....

    & 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