Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Join Date
    Sep 2003
    Location
    Washington, DC
    Posts
    116

    Angry Unanswered: Incorrect Punctuation MADNESS!!!

    I've been working on this code for about a week now, and cannot for the life of me figure out what is wrong with my SQL statement. My code stops on the first SQL statement and says Run-time error 3141: The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect.

    I've checked this thing a million times, and can't see anything wrong with it. I've had others check it, and they see nothing. All the field/table names referred to are spelled correctly. HELP!!!

    Private Function EnterAnswer(TAns As Integer)

    DoCmd.RunSQL "SELECT count(tblQuizTemp.TEmployeeID) AS NumOfRecords" & _
    "FROM tblQuizTemp" & _
    "WHERE TEmployeeID = " & [qzEmployeeID] & " AND TCourseID = " & [qzCourseID] & " AND TQuestionID = " & [qzQuestionID] & ";"

    If NumOfRecords > 0 Then
    If MsgBox("You have already answered this question. Do you want to change your answer?" _
    , vbYesNo + vbQuestion, "Your Title") = vbYes Then
    DoCmd.RunSQL "UPDATE tblQuizTemp " & _
    "SET TAnswer = " & TAns & _
    "WHERE TEmployeeID = " & [qzEmployeeID] & " AND TCourseID = " & [CourseID] & " AND TQuestionID = " & [QuestionID] & ";"
    End If
    Else
    DoCmd.RunSQL "INSERT INTO tblQuizTemp (TEmployeeID, TCourseID, TQuestionID, TAnswer ) " & _
    "SELECT " & [qzEmployeeID] & " AS Expr1, " & [CourseID] & " AS Expr2, " & [QuestionID] & " AS Exp3, " & TAns & " AS Exp4 ;"
    End If
    End Function

    Private Sub A_Click()
    Call EnterAnswer(1)
    End Sub

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    try a space before each line or after each "& _" effectively you are running word together so the sql error is correct it's reading your sql and finding puctuation errors"
    ie
    "FROM.... becomes " FROM....
    "WHERE... becomes " WHERE....
    etc...

    you could have found this by displaying the affected lines in the debug window

    Although I would <hate> to meniton style, I woudl reccomend that you build your sql as a sepearate stage
    eg
    strSQL="Select * from table"
    docmd.runquery strsql
    the reason - its easier to debug, just type ?strSQL in the debug window, rather than having to copy and paste.
    Last edited by healdem; 11-30-04 at 12:35.

  3. #3
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Here is how I eliminate this type of error in my LONG queries:

    Code:
        SQLString = "SELECT [SI Department Detail].Ndx, [SI Department Detail].DepartmentID, [CS Departments].Prefix, [SI Department Detail].Hours, [SI Department Detail].Completed, [SI Department Detail].CompletionDate, [SI Department Detail].Comments"
        SQLString = SQLString & " FROM [SI Department Detail] INNER JOIN [CS Departments] ON [SI Department Detail].DepartmentID = [CS Departments].DepartmentID"
        SQLString = SQLString & " WHERE (([SI Department Detail].WorkOrderNumber='" & SrcWorkOrder & "') AND ([SI Department Detail].[SI #]=" & SrcSI & "));"
    Back to Access ... ADO is not the way to go for speed ...

  4. #4
    Join Date
    Sep 2003
    Location
    Washington, DC
    Posts
    116
    I tried changing the coding to the following and now get a different error (Invalid SQL statement (looking for DELETE, INSERT, etc.)) on the docmd.RunSQL line.

    Private Function EnterAnswer(TAns As Integer)
    Dim strSQL As String

    strSQL = "SELECT count(tblQuizTemp.TEmployeeID) AS NumOfRecords" & _
    strSQL = strSQL & " FROM tblQuizTemp" & _
    strSQL = strSQL & " WHERE TEmployeeID = " & [qzEmployeeID] & " AND TCourseID = " & [qzCourseID] & " AND TQuestionID = " & [qzQuestionID] & ";"

    DoCmd.RunSQL strSQL

    If NumOfRecords > 0 Then
    If MsgBox("You have already answered this question. Do you want to change your answer?" _
    , vbYesNo + vbQuestion, "Your Title") = vbYes Then
    DoCmd.RunSQL "UPDATE tblQuizTemp " & _
    "SET TAnswer = " & TAns & _
    "WHERE TEmployeeID = " & [qzEmployeeID] & " AND TCourseID = " & [CourseID] & " AND TQuestionID = " & [QuestionID] & ";"
    End If
    Else
    DoCmd.RunSQL "INSERT INTO tblQuizTemp (TEmployeeID, TCourseID, TQuestionID, TAnswer ) " & _
    "SELECT " & [qzEmployeeID] & " AS Expr1, " & [CourseID] & " AS Expr2, " & [QuestionID] & " AS Exp3, " & TAns & " AS Exp4 ;"
    End If
    End Function

  5. #5
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by PatrickFAO
    I tried changing the coding to the following and now get a different error (Invalid SQL statement (looking for DELETE, INSERT, etc.)) on the docmd.RunSQL line.

    Private Function EnterAnswer(TAns As Integer)
    Dim strSQL As String

    strSQL = "SELECT count(tblQuizTemp.TEmployeeID) AS NumOfRecords" & _
    strSQL = strSQL & " FROM tblQuizTemp" & _
    strSQL = strSQL & " WHERE TEmployeeID = " & [qzEmployeeID] & " AND TCourseID = " & [qzCourseID] & " AND TQuestionID = " & [qzQuestionID] & ";"

    DoCmd.RunSQL strSQL

    If NumOfRecords > 0 Then
    If MsgBox("You have already answered this question. Do you want to change your answer?" _
    , vbYesNo + vbQuestion, "Your Title") = vbYes Then
    DoCmd.RunSQL "UPDATE tblQuizTemp " & _
    "SET TAnswer = " & TAns & _
    "WHERE TEmployeeID = " & [qzEmployeeID] & " AND TCourseID = " & [CourseID] & " AND TQuestionID = " & [QuestionID] & ";"
    End If
    Else
    DoCmd.RunSQL "INSERT INTO tblQuizTemp (TEmployeeID, TCourseID, TQuestionID, TAnswer ) " & _
    "SELECT " & [qzEmployeeID] & " AS Expr1, " & [CourseID] & " AS Expr2, " & [QuestionID] & " AS Exp3, " & TAns & " AS Exp4 ;"
    End If
    End Function
    Well YEAH! Try not to use the continuation symbol when you're coding the concatenation of the SQL statement ... Drop the "&_" from the end of your lines ...

    Ex:
    strSQL = "SELECT count(tblQuizTemp.TEmployeeID) AS NumOfRecords"
    strSQL = strSQL & " FROM tblQuizTemp"
    strSQL = strSQL & " WHERE ((TEmployeeID = " & [qzEmployeeID] & ") AND (TCourseID = " & [qzCourseID] & ") AND (TQuestionID = " & [qzQuestionID] & "));"
    Back to Access ... ADO is not the way to go for speed ...

  6. #6
    Join Date
    Sep 2003
    Location
    Washington, DC
    Posts
    116
    I'm still getting an error that a runSQL action requires a SQL statement.

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    do you want to report what your current sql statement is?

  8. #8
    Join Date
    Sep 2003
    Location
    Washington, DC
    Posts
    116
    Sorry!!

    strSQL = "SELECT count(tblQuizTemp.TEmployeeID) AS NumOfRecords"
    strSQL = strSQL & " FROM tblQuizTemp"
    strSQL = strSQL & " WHERE ((TEmployeeID = " & [qzEmployeeID] & ") AND (TCourseID = " & [qzCourseID] & ") AND (TQuestionID = " & [qzQuestionID] & "));"

  9. #9
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    RunSQL is for action queries (Update, Delete, Append, etc.) Your query is just a SELECT. You will probably want to use and OpenRecordset to check to see how many times the question was answered.

  10. #10
    Join Date
    Sep 2003
    Location
    Washington, DC
    Posts
    116
    Okay, now I'm getting an error when the strSQL is run if there is no record already. The basic gist is I want this thing to check to see if a record already exists in a table. If it does, then ask the user if they want to update their answer. If yes, then update the answer, if no then cancel. If a record doesn't already exist, I just want it to enter the info in the table. I've modified this code what feels like 1,000 times and it's just not cooperating...

    Private Function EnterAnswer(TAns As Integer)
    Dim strSQL As String

    strSQL = " SELECT count(tblQuizTemp.TEmployeeID) AS NumOfRecords"
    strSQL = strSQL & " FROM tblQuizTemp"
    strSQL = strSQL & " WHERE ((TEmployeeID = " & [qzEmployeeID] & ") AND (TCourseID = " & [qzCourseID] & ") AND (TQuestionID = " & [qzQuestionID] & "));"

    If NumOfRecords > 0 Then
    If MsgBox("You have already answered this question. Do you want to change your answer?" _
    , vbYesNo + vbQuestion, "Your Title") = vbYes Then
    DoCmd.RunSQL "UPDATE tblQuizTemp " & _
    "SET TAnswer = " & TAns & _
    "WHERE TEmployeeID = " & [qzEmployeeID] & " AND TCourseID = " & [CourseID] & " AND TQuestionID = " & [QuestionID] & ";"
    End If
    Else
    DoCmd.SetWarnings False
    DoCmd.RunSQL "INSERT INTO tblQuizTemp (TEmployeeID, TCourseID, TQuestionID, TAnswer ) " & _
    "SELECT " & [qzEmployeeID] & " AS Expr1, " & [CourseID] & " AS Expr2, " & [QuestionID] & " AS Exp3, " & TAns & " AS Exp4 ;"
    DoCmd.SetWarnings True
    End If

    DoCmd.Requery strSQL

    End Function

  11. #11
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    you can't .runSQL on a SELECT - where do you expect access to place the thousands of records potentially returned by SELECT

    here are some ways to achieve your record count:

    1. feed SELECT into a recordset
    DAO example:
    dim dabs as dao.database
    dim recs as dao.recordset
    set dabs = currentdb
    set recs = dabs.openrecordset(strSQL)
    recs.movelast
    myCount = recs.recordcount
    set recs = nothing
    set dabs = nothing

    2. feed a SELECT COUNT() into a recordset - you only get one record but access doesn't "know" this so you still can't feed it directly to a variable
    DAO again using your strSQL "SELECT count( etc... as NumOfRecs ...etc etc
    dim dabs as dao.database
    dim recs as dao.recordset
    set dabs = currentdb
    set recs = dabs.openrecordset(strSQL)
    if (recs.eof and recs.bof) then
    myCount = 0
    else
    myCount = recs!NumOfRecs
    endif

    3. domain agregate functions - these do a similar job to SQL but return a single value that slots happily directly into a variable:
    myCount = DCOUNT("TEmployeeID", "tblQuizTemp", "((TEmployeeID = " & [qzEmployeeID] & ") AND (TCourseID = " & [qzCourseID] & ") AND (TQuestionID = " & [qzQuestionID] & "))")



    izy
    Last edited by izyrider; 12-01-04 at 12:19.
    currently using SS 2008R2

  12. #12
    Join Date
    Sep 2003
    Location
    Washington, DC
    Posts
    116
    I tried using the domain aggregate function you provided, but I still get an error saying that I cancelled the previous operation when it gets to the DCount.

    Private Function EnterAnswer(TAns As Integer)
    Dim myCount As Integer

    myCount = DCount("TEmployeeID", "tblQuizTemp", "((TEmployeeID = " & [qzEmployeeID] & ") AND (TCourseID = " & [qzCourseID] & ") AND (TQuestionID = " & [qzQuestionID] & "))")

    If myCount > 0 Then
    If MsgBox("You have already answered this question. Do you want to change your answer?" _
    , vbYesNo + vbQuestion, "Your Title") = vbYes Then
    DoCmd.RunSQL "UPDATE tblQuizTemp " & _
    "SET TAnswer = " & TAns & _
    "WHERE TEmployeeID = " & [qzEmployeeID] & " AND TCourseID = " & [CourseID] & " AND TQuestionID = " & [QuestionID] & ";"
    End If
    Else
    DoCmd.SetWarnings False
    DoCmd.RunSQL "INSERT INTO tblQuizTemp (TEmployeeID, TCourseID, TQuestionID, TAnswer ) " & _
    "SELECT " & [qzEmployeeID] & " AS Expr1, " & [CourseID] & " AS Expr2, " & [QuestionID] & " AS Exp3, " & TAns & " AS Exp4 ;"
    DoCmd.SetWarnings True
    End If

    End Function

  13. #13
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    have you checked to ensure that all your paramters are valid

    ie the parameters the [qz...] have valid data in if null then dlookup wil through an error similar to the user cancelled the last operation

    personally I'd run a mile before using any domain construct, i find 'em pityfully slow

  14. #14
    Join Date
    Sep 2003
    Location
    Washington, DC
    Posts
    116
    My parameters are valid. For the strSQL statement, I created it as a query first, and then pasted it into VB.

    To be honest, I'm not VB savvy enough yet to be able to tell the difference between domain constructs and sql statements when it comes to speed. I know sql, and therefore tend to use that when writing anything in VB.

    Would it be helpful to have a cooy of the database?

  15. #15
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    i don't see an obvious error.

    lets do it step-by-step:

    Private Function AnyNewNameYouLike(TAns As Integer)
    on error goto err_enteranswer
    Dim myCount As Integer
    Dim myWhere as string
    myWhere = "((TEmployeeID = " & [qzEmployeeID] & ") AND (TCourseID = " & [qzCourseID] & ") AND (TQuestionID = " & [qzQuestionID] & "))"
    msgbox myWhere, , "Does this look OK?"
    myCount = DCount("TEmployeeID", "tblQuizTemp", myWhere)
    msgbox "MyCount is " & myCount, , "Did it work?"
    err_enteranswer:
    msgbox err.description , , "Error #" & err.number
    end function

    use a different function name just in case A got itself lost.

    ....what do you see?

    izy
    currently using SS 2008R2

Posting Permissions

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