Invalid SQL Statement

  • Thread starter Thread starter Guest
  • Start date Start date
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---]
 
Without going through every line of code, the first step I suggest is that
you look at the SQL statement that is printed in the Debug.Print step. I'm
guessing that there is a misplaced parenthesis, or a missing blank, or
something, that makes the SQL statement have invalid syntax. If you copy the
statement that is printed, and paste it into a new query (SQL view), does it
return the expected rows? or does it error?

--

Ken Snell
<MS ACCESS MVP>

Mattantaliss said:
[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---]
Click to expand...
 
the first problem is that the RunSQL method is for action queries (append,
delete, update, make table) It does not work for select queries.

This should have been your clue:
"Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT',
or 'UPDATE'."
Mattantaliss said:
[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---]
Click to expand...
 
But in that "Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE',
'SELECT', or 'UPDATE'." error message, it has 'SELECT' as an option, so I
assumed I would be able to run such a query.
Is there a function similar to RunSQL that works for select queries?

matt

Klatuu said:
the first problem is that the RunSQL method is for action queries (append,
delete, update, make table) It does not work for select queries.

This should have been your clue:
"Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT',
or 'UPDATE'."
Mattantaliss said:
[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---]
Click to expand...
Click to expand...
 
The help info I sent is sort of incorrect.
There is not such method for a select query. There are two things you can
do with a select query. You can create a saved query just as if you had used
the query builder or you can create a recordset.

Set rst = CurrentDB.OpenRecordset(strSQL)

What is it you want to do once you have the query built?

Mattantaliss said:
But in that "Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE',
'SELECT', or 'UPDATE'." error message, it has 'SELECT' as an option, so I
assumed I would be able to run such a query.
Is there a function similar to RunSQL that works for select queries?

matt

Klatuu said:
the first problem is that the RunSQL method is for action queries (append,
delete, update, make table) It does not work for select queries.

This should have been your clue:
"Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT',
or 'UPDATE'."
Mattantaliss said:
[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---]
Click to expand...
Click to expand...
Click to expand...
 
I tried substituting
Set rst = CurrentDB.OpenRecordset(strSQL)
for the RunSQL statement I had, but I am now receiving an error about having
too few parameters (should be 4). I've searched around a little to try and
find an explanation of the parameters to pass to OpenRecordset, but haven't
found anything useful yet.

For the moment, I am just trying to get this 'create a query from within the
code' working insteading of having a pre-built query. The pre-built query
way, it seems, is way too static for my purpose. I need to allow someone to
decide which stats s/he would like to see and for which date range and
summary level.
Ultimately, I am thinking I want to get this to the point where one would be
able to select the desired information from the form and have an Excel
spreadsheet generated (possibly a Word document; not sure which would be
better yet).

matt


Klatuu said:
The help info I sent is sort of incorrect.
There is not such method for a select query. There are two things you can
do with a select query. You can create a saved query just as if you had used
the query builder or you can create a recordset.

Set rst = CurrentDB.OpenRecordset(strSQL)

What is it you want to do once you have the query built?

Mattantaliss said:
But in that "Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE',
'SELECT', or 'UPDATE'." error message, it has 'SELECT' as an option, so I
assumed I would be able to run such a query.
Is there a function similar to RunSQL that works for select queries?

matt

Klatuu said:
the first problem is that the RunSQL method is for action queries (append,
delete, update, make table) It does not work for select queries.

This should have been your clue:
"Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT',
or 'UPDATE'."
:

[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---]
Click to expand...
Click to expand...
Click to expand...
 
FYI: The error message is misleading. Rather than "Expected ..., SELECT, "
it should have said "Expected ..., SELECT...INTO,". SELECT...INTO is a Make
Table query, and is classified as an Action query.

HTH,
--
George Nicholson

Remove 'Junk' from return address.


Mattantaliss said:
But in that "Invalid SQL statement; expected 'DELETE', 'INSERT',
'PROCEDURE',
'SELECT', or 'UPDATE'." error message, it has 'SELECT' as an option, so I
assumed I would be able to run such a query.
Is there a function similar to RunSQL that works for select queries?

matt

Klatuu said:
the first problem is that the RunSQL method is for action queries
(append,
delete, update, make table) It does not work for select queries.

This should have been your clue:
"Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE',
'SELECT',
or 'UPDATE'."
Mattantaliss said:
[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---]
Click to expand...
Click to expand...
Click to expand...
 
It has to do with the construct of your SQL. I am leaving the building
(Elvis is waiting), so I can't look at it detail at the moment. I did notice
that in the beginning, you select either nothing, agent, supervisor, or floor
manager. Then when you do the group by you are grouping by all. I don't
know for sure if that is a problem, but I think it might. We will look at it
again on Monday.

Mattantaliss said:
I tried substituting
Set rst = CurrentDB.OpenRecordset(strSQL)
for the RunSQL statement I had, but I am now receiving an error about having
too few parameters (should be 4). I've searched around a little to try and
find an explanation of the parameters to pass to OpenRecordset, but haven't
found anything useful yet.

For the moment, I am just trying to get this 'create a query from within the
code' working insteading of having a pre-built query. The pre-built query
way, it seems, is way too static for my purpose. I need to allow someone to
decide which stats s/he would like to see and for which date range and
summary level.
Ultimately, I am thinking I want to get this to the point where one would be
able to select the desired information from the form and have an Excel
spreadsheet generated (possibly a Word document; not sure which would be
better yet).

matt


Klatuu said:
The help info I sent is sort of incorrect.
There is not such method for a select query. There are two things you can
do with a select query. You can create a saved query just as if you had used
the query builder or you can create a recordset.

Set rst = CurrentDB.OpenRecordset(strSQL)

What is it you want to do once you have the query built?

Mattantaliss said:
But in that "Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE',
'SELECT', or 'UPDATE'." error message, it has 'SELECT' as an option, so I
assumed I would be able to run such a query.
Is there a function similar to RunSQL that works for select queries?

matt

:

the first problem is that the RunSQL method is for action queries (append,
delete, update, make table) It does not work for select queries.

This should have been your clue:
"Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT',
or 'UPDATE'."
:

[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---]
Click to expand...
Click to expand...
Click to expand...
 
Sounds like a plan... see ya on Monday.

matt

Klatuu said:
It has to do with the construct of your SQL. I am leaving the building
(Elvis is waiting), so I can't look at it detail at the moment. I did notice
that in the beginning, you select either nothing, agent, supervisor, or floor
manager. Then when you do the group by you are grouping by all. I don't
know for sure if that is a problem, but I think it might. We will look at it
again on Monday.

Mattantaliss said:
I tried substituting
Set rst = CurrentDB.OpenRecordset(strSQL)
for the RunSQL statement I had, but I am now receiving an error about having
too few parameters (should be 4). I've searched around a little to try and
find an explanation of the parameters to pass to OpenRecordset, but haven't
found anything useful yet.

For the moment, I am just trying to get this 'create a query from within the
code' working insteading of having a pre-built query. The pre-built query
way, it seems, is way too static for my purpose. I need to allow someone to
decide which stats s/he would like to see and for which date range and
summary level.
Ultimately, I am thinking I want to get this to the point where one would be
able to select the desired information from the form and have an Excel
spreadsheet generated (possibly a Word document; not sure which would be
better yet).

matt


Klatuu said:
The help info I sent is sort of incorrect.
There is not such method for a select query. There are two things you can
do with a select query. You can create a saved query just as if you had used
the query builder or you can create a recordset.

Set rst = CurrentDB.OpenRecordset(strSQL)

What is it you want to do once you have the query built?

:

But in that "Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE',
'SELECT', or 'UPDATE'." error message, it has 'SELECT' as an option, so I
assumed I would be able to run such a query.
Is there a function similar to RunSQL that works for select queries?

matt

:

the first problem is that the RunSQL method is for action queries (append,
delete, update, make table) It does not work for select queries.

This should have been your clue:
"Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT',
or 'UPDATE'."
:

[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---]
Click to expand...
Click to expand...
Click to expand...
 
If you're just going for changing the SQL of a saved query on the fly then
it's....

dim db as database
dim qdf as querydef
set db=currentdb
set qdf=db.querydefs("QueryName")
qdf.sql="select whatever from whereever"


Mattantaliss said:
I tried substituting
Set rst = CurrentDB.OpenRecordset(strSQL)
for the RunSQL statement I had, but I am now receiving an error about having
too few parameters (should be 4). I've searched around a little to try and
find an explanation of the parameters to pass to OpenRecordset, but haven't
found anything useful yet.

For the moment, I am just trying to get this 'create a query from within the
code' working insteading of having a pre-built query. The pre-built query
way, it seems, is way too static for my purpose. I need to allow someone to
decide which stats s/he would like to see and for which date range and
summary level.
Ultimately, I am thinking I want to get this to the point where one would be
able to select the desired information from the form and have an Excel
spreadsheet generated (possibly a Word document; not sure which would be
better yet).

matt


Klatuu said:
The help info I sent is sort of incorrect.
There is not such method for a select query. There are two things you can
do with a select query. You can create a saved query just as if you had used
the query builder or you can create a recordset.

Set rst = CurrentDB.OpenRecordset(strSQL)

What is it you want to do once you have the query built?

Mattantaliss said:
But in that "Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE',
'SELECT', or 'UPDATE'." error message, it has 'SELECT' as an option, so I
assumed I would be able to run such a query.
Is there a function similar to RunSQL that works for select queries?

matt

:

the first problem is that the RunSQL method is for action queries (append,
delete, update, make table) It does not work for select queries.

This should have been your clue:
"Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT',
or 'UPDATE'."
:

[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 &
Click to expand...
Click to expand...
Click to expand...
"Sum([tblPerformanceRpt].[CallsTaken]*[tblPerformanceRpt].[AHT])/Sum([tblPer
formanceRpt].[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---]
Click to expand...
Click to expand...
Click to expand...
 
Back
Top