Hi all, I've hit a roadblock with this code. I can't get the query to run. The Error that continues to pop up for this code is "Runtime error 3142 - Characters found at end of SQL statement". Debugger lands on ****Set qdf line near the bottom.
I've scoured the web for a solution with no success. I've deleted/added the semicolon at the end which ends up in yet another error.
Code:
strSQL = "SELECT [Event Date], [Start Time], [Event Length min], [Origin Bank A1], " & _
"[Origin Bank A2], [Origin Participant A1], [Origin Participant A2], [Origin Participant A3], " & _
"[Origin PCSS A1], [Origin PCSS A2], [Origin Other], [Type of Problem Process A1], " & _
"[Type of Problem Process A2], [Type of Problem Human A1], [Type of Problem Human A2], [Type of Problem External A1], " & _
"[Type of Problem Systems A1], [Type of Problem Systems A2], [Type of Problem Systems A3], [Impact on Source Miscellaneous A1], " & _
"[Impact on Source Miscellaneous A2], [Impact on Source Payments Delayed A1], [Impact on Source Payments Delayed A2], [Impact on Source Fund Management Delayed], " & _
"[Impact on Source Advance], [Who Affected BOC A1], [Who Affected BOC A2], [Who Affected BOC A3], " & _
"[Who Affected Participant A1], [Who Affected Participant A2], [Who Affected Participant A3], [Who Affected Participant A4]," & _
"[Who Affected Participant A5], [Who Affected Participant A6], [Who Affected Participant A7], [Who Affected Participant A8]," & _
"[Who Affected Participant A9], [Who Affected PCSS A1], [Who Affected PCSS A2], [Who Affected Others A1], " & _
"[Who Affected Others A2], [Impact on affected operations A1], [Impact on affected operations A2], [Impact on affected Payments Delayed A1], " & _
"[Impact on affected Payments Delayed A2], [Impact on affected Payments Delayed A3], [Impact on affected Fund Management], [Impact on affected Advances], " & _
"[Consequences A1], [Consequences A2], LVTS_Operationl_Event_Database.[Consequences A3], [Consequences A4], " & _
"[Consequences A5], [Action Taken by B andor C A1], [Action Taken by B andor C A2], [Action Taken by B andor C A3], " & _
"[Implemented Contingency on Which System], [Actual Severity of the OE], [Potentially affected BOC A1], [Potentially affected B A2], " & _
"[Potentially affected B A3], [Potentially affected B A4], [Potentially Affected Participant A1], [Potentially Affected Participant A2], " & _
"[Potentially Affected Participant A3], [Potentially Affected Participant A4], LVTS_Opeational_Event_Database.[Potentially Affected Participant A5], [Potentially Affected Participant A6], " & _
"[Potentially Affected Participant A7], [Potentially Affected Participant A8], [Potentially Affected Participant A9], [Potentially Affected PCSS A1], " & _
"[Potentially Affected PCSS A2], [Potentially Affected PCSS A3], [Potentially Affected PCSS A4], [Potentially Affected PCSS A5], " & _
"[Potentially Affected PCSS A6], [Potentially Affected Others], [Potential Impact from the OE A1], [Potential Impact from the OE A2], " & _
"[Potential Impact on Payment Delayed A1], [Potential Impact on Payment Delayed A2], [Potential Impact on Payment Delayed A3], [Potential Impact on Payment Delayed A4], " & _
"[Potential Impact on Funds Management A1], [Potential Impact on Funds Management A2], [Potential Impact on Advances], [Potential Severity of the OE], " & _
"[Follow up informations and References A1], [Follow up informations and References A2], [Follow up informations and References A3], [Follow up informations and References A4], " & _
"[Follow up informations and References A5], [File Status] FROM tb_LVTS_Operational_Event_Database;"
strOrder = "ORDER BY tb_LVTS_Operational_Event_Database.[Date], tb_LVTS_Operational_Event_Database.[Start Time], " & _
"tb_LVTS_Operational_Event_Database.[Event Length];"
strSQL = strSQL & strWhere & strOrder
'MsgBox "strSQL"
'********************
'Use to display the SQL string prior to running the query
'MsgBox strSQL
'Create a new query
Dim qdf As QueryDef
Dim strQueryToRun As String
strQueryToRun = "NewQuery"
'Delete old query if one exists
For Each qdf In CurrentDb.QueryDefs
If qdf.Name = strQueryToRun Then
CurrentDb.QueryDefs.Delete qdf.Name
Else
End If
Next qdf
'Run the new query
With CurrentDb
****Set qdf = .CreateQueryDef(strQueryToRun, strSQL)
DoCmd.OpenQuery qdf.Name
'.QueryDefs.Delete qdf.Name
End With
Set qdf = Nothing
End Sub