Invalid SQL Statement

Tech-Archive recommends: Fix windows errors by optimizing your registry

[With Access 2000]
I am attempting to construct an SQL statement in the VB code for a form, and
then run that instead of a pre-built query, but I am running across an error
that I can not seem to get past. With the code I have pasted below I am
getting the following run-time error:
"A RunSQL action requires an argument of an SQL statement."

If I change the line [see code below]
DoCmd.RunSQL sSQL, False
to
DoCmd.RunSQL "sSQL", False
then I get the error:
"Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE',
'SELECT', or 'UPDATE'."

The debug statement immediately preceding this particular line seems to
confirm that what I have constructed with the code up to then is in fact a
valid SQL statement. So I am at a loss as to what I need to do to go about
correcting this error. Any suggestions?

matt

[---Begin Code---]
Private Sub cmdSearch_Click()
On Error GoTo Err_cmdSearch_Click

Dim sSelections As String
Dim sSQL As String
Dim varitem As Variant

sSelections = ""
'Loop through all the items that were selected and append to sSelections
For Each varitem In lstResults.ItemsSelected
sSelections = sSelections & "," & lstResults.ItemData(varitem)
Next varitem
'Remove initial comma
sSelections = Mid(sSelections, 2)

'Begin to build up SQL statement
sSQL = "SELECT DISTINCTROW "

'Determine which level to summarize
Select Case grpSummaryLevel
'Case 1 is for Enterprise level summary, so add nothing more to the SQL
statement
Case 2 'Floor Manager level
sSQL = sSQL & "[tblFloorManager].[tblFloorManagerName], "
Case 3 'Supervisor level
sSQL = sSQL & "[tblSupervisor].[tblSupervisorName], "
Case 4 'Agent level
sSQL = sSQL & "[tblAgent].[tblAgentName], "
End Select

'Determine which stats to show
If chkSignOnHours Then
sSQL = sSQL & "Avg([tblPerformanceRpt].[SignOnHours]) AS [Avg Sign-On
Hours], "
End If
If chkCallsTaken Then
sSQL = sSQL & "Avg([tblPerformanceRpt].[CallsTaken]) AS [Avg Calls
Taken], "
End If
If chkAHT Then
sSQL = sSQL &
"Sum([tblPerformanceRpt].[CallsTaken]*[tblPerformanceRpt].[AHT])/Sum([tblPerformanceRpt].[CallsTaken])" & _
" AS [AHT], "
End If
If chkAvailable Then
sSQL = sSQL & "Avg([tblPerformanceRpt].[Available%]) AS [Avg Available
%], "
End If
If chkIdle Then
sSQL = sSQL & "Avg([tblPerformanceRpt].[Idle%]) AS [Avg Idle %], "
End If
If chkWrap Then
sSQL = sSQL & "Avg([tblPerformanceRpt].[Wrap%]) AS [Wrap %], "
End If
'--->Add in a catch error for case when nothing is selected

'Trim off last comma + space and add a space back on
sSQL = Left(sSQL, Len(sSQL) - 2) & " "

'Continue building up SQL statement (FROM section)
sSQL = sSQL & "FROM [tblFloorManager] INNER JOIN ([tblSupervisor] INNER JOIN
([tblAgent] INNER JOIN [tblPerformanceRpt] ON" & _
" [tblAgent].[UID] = [tblPerformanceRpt].[UID]) ON
[tblSupervisor].[SupervisorUID] = [tblAgent].[SupervisorUID]) ON" & _
" [tblFloorManager].[FloorManagerUID] =
[tblSupervisor].[FloorManagerUID] "

'Continue building up SQL statement (WHERE section)
sSQL = sSQL & "WHERE (((inparam([tblAgent].[UID],sSelections))=True) OR" & _
" ((inparam([tblSupervisor].[SupervisorUID],sSelections))=True) OR" & _
" ((inparam([tblFloorManager].[FloorManagerUID],sSelections))=True) AND"
& _
" (txtBeginDate<=[tblPerformanceRpt].[Date]) AND" & _
" (txtEndDate>=[tblPerformanceRpt].[Date])) "

'Complete building with the GROUP BY section
sSQL = sSQL & "GROUP BY [tblAgent].[AgentName],
[tblSupervisor].[SupervisorName], [tblFloorManager].[FloorManagerName];"

'Run query
Debug.Print sSQL

DoCmd.RunSQL sSQL, False

Exit_cmdSearch_Click:
Exit Sub

Err_cmdSearch_Click:
MsgBox Err.Description
Resume Exit_cmdSearch_Click

End Sub
[---End Code---]
.

Relevant Pages

  • RE: Invalid SQL Statement
    ... the first problem is that the RunSQL method is for action queries (append, ... > "A RunSQL action requires an argument of an SQL statement." ... > DoCmd.RunSQL "sSQL", False ...
    (microsoft.public.access.formscoding)
  • Oracle connection trouble
    ... I was originally using a UDL file for the ... I decided to put a connection string into my ... I can ?sSql in my Immediate pane and C&P the SQL statement ...
    (microsoft.public.access.modulesdaovba)
  • Re: Invalid SQL Statement
    ... you look at the SQL statement that is printed in the Debug.Print step. ... > DoCmd.RunSQL "sSQL", False ... > Dim varitem As Variant ... > 'Loop through all the items that were selected and append to sSelections ...
    (microsoft.public.access.formscoding)
  • Re: Audit Trail
    ... jacob, that's the line from the code. ... Debug.Print sSQL ... You have traced it down now to the SQL statement not executing. ... >> the same type, in the same order, and with the temp fields as specified ...
    (microsoft.public.access.formscoding)
  • RE: Invalid SQL Statement
    ... There is not such method for a select query. ... >>> DoCmd.RunSQL sSQL, False ... >>> valid SQL statement. ... >>> Dim varitem As Variant ...
    (microsoft.public.access.formscoding)