G
Guest
[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---]
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---]