RE: Invalid SQL Statement

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" wrote:

> 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" wrote:
>
> > [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: "Query Too Complex" Errors
    ... few dozens of queries, in the middle of which there's a long chain of ... we've been having a lot of those "Query Too ... some of the complexity in the SQL ... SQL statement you are working on. ...
    (microsoft.public.access.forms)
  • Re: Queries in VBA
    ... After you build the SQL statement as a string, ... queries a table by ... uses the recordset just queried as its record set. ... something similar in appropriate query fields. ...
    (microsoft.public.access.modulesdaovba)
  • Re: Inline CrossTab Query
    ... The SQL I used I just copied from the three queries in the Query Design ... >> I am trying to produce a SQL statement to bring together 3 queries ... >> I am getting an error message that says there is an Error in the ...
    (microsoft.public.access.queries)
  • Re: Bind forms to query or sql statement
    ... entering/editing all data using queries to now use forms instead. ... A query is simply a saved SQL statement. ... RecordSource to be independent, and not subject to being inadvertently ...
    (comp.databases.ms-access)
  • Re: Invalid SQL Statement
    ... Table query, and is classified as an Action query. ... >>> DoCmd.RunSQL sSQL, False ... >>> valid SQL statement. ...
    (microsoft.public.access.formscoding)