Results 1 to 10 of 10
  1. #1
    Join Date
    Feb 2012
    Posts
    108
    Provided Answers: 1

    Unanswered: Characters Found After End Of SQL Statement

    Hi all,

    Using Excel/Access 2007 w/reference to ADO 2.7 Library.

    I am trying to build some better SQL syntax but I keep getting an error message
    Characters found after end of SQL statement
    What am I doing wrong?

    Code:
         strSQL = ""
         strSQL = strSQL & " SELECT tblO.RID;"
         strSQL = strSQL & " tblO.CustNm;"
         strSQL = strSQL & " tblO.AID;"
         strSQL = strSQL & " tblO.STATUS;"
         strSQL = strSQL & " tblO.REG;"
         strSQL = strSQL & " tblO.SREG;"
         strSQL = strSQL & " tblO.CNTRY"
         strSQL = strSQL & " FROM tblO"
    
         With cnt
            .Open (stConn) 'Open the connection.
            .CursorLocation = adUseClient 'Necessary to disconnect the recordset.
         End With
    
         With rst
            .Open strSQL, cnt 'Create the recordset.
            Set .ActiveConnection = Nothing 'Disconnect the recordset.
         End With
    thx
    w

  2. #2
    Join Date
    May 2006
    Posts
    31
    Need to remove the ':" from the ends of the strsql -

    tblO.REG;
    tblO.SREG;, etc.

    K

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    ; is the statement termnator in SQL
    theoretically every SQL statement should terminate in a ; but many SQL engines don't worry about that.
    its definitely an issue if you want to run more than one SQL statement in one execution. something you can't do in access. its also one of the vectors of a SQL injection attack in many websites where another SQL statement can be tacked onto the end of user input.
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Use a comma (,) to separate each column name and, as healdem pointed out, a semicolon to end the SQL statement.

    If the string variable strsql was not previously used before in the sub/function the first of these statements is useless: Whether you explicitly declare a variable or not, it is automatically initialized in the BASIC family of languages. Notice that it's considered a good practice to declare a variable before using it. The second statement should be simplified because you do not need to concatenate an empty (zero-lenght) string with a litteral string. So:
    Code:
    strSQL = ""
         strSQL = strSQL & " SELECT tblO.RID;"
    Should become:
    Code:
    strSQL = "SELECT tblO.RID,"
    Alltogether we have:
    Code:
        Dim strSQL As String    
        
        strSQL = "SELECT tblO.RID,"
        strSQL = strSQL & " tblO.CustNm,"
        strSQL = strSQL & " tblO.AID,"
        strSQL = strSQL & " tblO.STATUS,"
        strSQL = strSQL & " tblO.REG,"
        strSQL = strSQL & " tblO.SREG,"
        strSQL = strSQL & " tblO.CNTRY"
        strSQL = strSQL & " FROM tblO;"
    Or in a way I find more readable (just a matter of taste):
    Code:
        Dim strSQL As String
        
        strSQL = "SELECT tblO.RID, " & _
                        "tblO.CustNm, " & _
                        "tblO.AID, " & _
                        "tblO.STATUS, " & _
                        "tblO.REG, " & _
                        "tblO.SREG, " & _
                        "tblO.CNTRY " & _
                 "FROM tblO;"
    Or simply:
    Code:
        Dim strSQL As String
        
        strSQL "SELECT tblO.RID, tblO.CustNm, tblO.AID, tblO.STATUS, tblO.REG, tblO.SREG, tblO.CNTRY FROM tblO;"
    As the contents of strSQL is purely static, you could consider using a constant instead of a variable:
    Code:
        Const c_SQL As String = "SELECT tblO.RID, tblO.CustNm, tblO.AID, tblO.STATUS, tblO.REG, tblO.SREG, tblO.CNTRY FROM tblO;"
    Have a nice day!

  5. #5
    Join Date
    Feb 2012
    Posts
    108
    Provided Answers: 1
    Thanks all,

    I revised based on Sinndho's recommendations. Now I receives this error message
    Run-time error "-2147217904 (80040e10)':
    No value given for one or more of the required parameters.
    Debug points here
    Code:
     .Open strSQL, cnt 'Create the recordset.
    All revised:
    Code:
    strSQL = "SELECT tblO.RID, " & _
                        "tblO.CustNm, " & _
                        "tblO.AID, " & _
                        "tblO.CustStatus, " & _
                        "tblO.RGN, " & _
                        "tblO.SRGN, " & _
                        "tblO.CNTRY " & _
                  "FROM tblO;"
         
         With cnt
            .Open (stConn) 'Open the connection.
            .CursorLocation = adUseClient 'Necessary to disconnect the recordset.
         End With
    
         With rst
            .Open strSQL, cnt 'Create the recordset.
            Set .ActiveConnection = Nothing 'Disconnect the recordset.
         End With

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    It could be a typo. Did you check that the names of the columns are correct?
    Have a nice day!

  7. #7
    Join Date
    Feb 2012
    Posts
    108
    Provided Answers: 1
    Thanks Sinndho,

    I did have a few different Field Headers in the destination table
    Updated the SQL and works swimmingly.

    thanks,
    w

  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

  9. #9
    Join Date
    Apr 2015
    Posts
    1

    Characters Found After End of SQL Statement Issue

    I am getting an error message that there are Characters Found After End of SQL Statement in the ParamToPT Sub:

    I included the entire script in case I am missing something.

    I am trying to make a report for each unit and then send an email out to that unit. For instance, the "No Shows" for Company A get send to Company A and the "No Shows" for Company B get sent to Company B. Company A and B are Report Units.

    Thanks.

    Public Sub MakeReport()

    DoCmd.SetWarnings False
    Dim Unit, Statement As DAO.Recordset
    Dim IntCount, IntLoop As Integer
    Dim ol As Outlook.Application
    Dim msg As MailItem
    Dim DateString As Date
    Set ol = New Outlook.Application

    DoCmd.OpenQuery "qry_Email_Due"
    'On Error Resume Next
    'Grab Next Unit
    IntCount = 0
    Set Unit = CurrentDb.OpenRecordset("tbl_EmailDue")
    Set Statement = CurrentDb.OpenRecordset("tbl_Email_Body")

    Unit.MoveFirst
    If Not (Unit.BOF And Unit.EOF) Then

    IntCount = Unit.RecordCount

    For IntLoop = 1 To IntCount

    'Rewrites the SQL statement to the next unit in the list
    Call ParamToPT("qry_By_BDE_NoShow", "Report_Unit = " & "'" & ((Unit("Report_Unit"))) & "'")
    Call ParamToPT("qry_By_BDE_Pending", "Report_Unit = " & "'" & ((Unit("Report_Unit"))) & "'")

    'Creates the report as a .pdf file in the same directory as the database.
    DoCmd.OutputTo acOutputReport, "By_BDE_NoShow", ".pdf", CurrentProject.Path & "\" & Unit("REPORT_UNIT") & " No_Show" & ".pdf", False
    DoCmd.OutputTo acOutputReport, "By_BDE_Pending", ".pdf", CurrentProject.Path & "\" & Unit("REPORT_UNIT") & " Upcoming" & ".pdf", False

    ' Send No-Show Report
    Dim Ebody As Variant

    Ebody = DLookup("[Body]", "tbl_Email_Body", "[Email_Type] = 'No_Show'")

    Set msg = ol.CreateItem(olMailItem)
    With msg
    .To = Unit("POC_Email")
    .Subject = Unit("Subject_NoShow") & " for " & Unit("REPORT_UNIT") & " (UNCLASSIFIED)"
    .Body = Ebody
    .Attachments.Add CurrentProject.Path & "\" & Unit("REPORT_UNIT") & " No_Show" & ".pdf"
    .Save
    End With

    'Sending Pending Report
    Ebody = DLookup("[Body]", "tbl_Email_Body", "[Email_Type] = 'Pending'")
    Set msg = ol.CreateItem(olMailItem)
    With msg
    .To = Unit("POC_Email")
    .Subject = Unit("Subject_Pending") & " for " & Unit("REPORT_UNIT") & " (UNCLASSIFIED)"
    .Body = Ebody
    .Attachments.Add CurrentProject.Path & "\" & Unit("REPORT_UNIT") & " Upcoming" & ".pdf"
    .Save
    End With


    Unit.MoveNext

    Next IntLoop
    End If



    End Sub

    Public Sub ParamToPT(strQueryName As String, strClause As String)
    Dim strSQL As String
    Dim intPosn As Integer

    Dim db As DAO.Database
    Dim qd As DAO.QueryDef
    Set db = CurrentDb
    Set qd = db.QueryDefs(strQueryName)

    strSQL = qd.SQL
    ' in case the existing version has been saved with a Where Clause
    ' Strip the Where clause off the end
    intPosn = InStr(strSQL, "WHERE")
    If intPosn > 0 Then
    strSQL = Left(strSQL, intPosn - 1)
    End If
    ' Now add the new Where clause
    strSQL = Trim(strSQL) & " WHERE " & strClause

    ' now update the query code itself
    qd.SQL = strSQL

    Set qd = Nothing
    Set db = Nothing


    End Sub

  10. #10
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    What are the SQL expressions in qry_By_BDE_NoShow and qry_By_BDE_Pending?
    Have a nice day!

Posting Permissions

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