Re: Can SQL statement using a query rather than a table?

Tech-Archive recommends: Speed Up your PC by fixing your registry


The square brackets are not required unless there are spaces or other
problematic characters in the name. If you think you need them in your
dynamic SQL statement, you need to keep them within the quotes ...

Set rs1 = dbs.OpenRecordset("SELECT * FROM [" & QueryName & "] ORDER BY
Field1")

--
Brendan Reynolds (MVP)

"Jim in Northwest" <JiminNorthwest@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
message news:03F8BED6-45AA-44F3-B36D-66B6A23C6B85@xxxxxxxxxxxxxxxx
> Hi, Brendan:
>
> [] works fine for the query in the SQL Statement. In my application, the
> query name is actually an arugment passing through a general procedure.
>
> For example:
> Public sub MyProcedure (QueryName as String)
> .....
> Set rs1 = dbs.OpenRecordset _
> ("SELECT * FROM " & [QueryName] & " ORDER BY Field1")
> .....
>
> or I tried:
> .......
> Dim aa as string
> aa = QueryName
>
> Set rs1 = dbs.OpenRecordset _
> ("SELECT * FROM " & [aa] & " ORDER BY Field1")
>
> Non of them seems working. What syntex should they be?
> Many thanks.
>
>
>
> --
> Jim in Northwest
>
>
> "Brendan Reynolds" wrote:
>
>>
>> The example below works for me, using the 'Quarterly Orders by Product'
>> cross-tab query from Northwind. As I said, if you still can't get it to
>> work, try posting the SQL for the 'MyQuery' query. Does that query work
>> on
>> it's own?
>>
>> Public Sub TestSubX()
>>
>> Dim db As DAO.Database
>> Dim rst As DAO.Recordset
>> Dim strSQL As String
>>
>> strSQL = "SELECT * FROM [Quarterly Orders by Product] ORDER BY
>> CustomerID"
>> Set db = CurrentDb
>> Set rst = db.OpenRecordset(strSQL)
>> Debug.Print rst.Fields("CustomerID")
>> rst.Close
>>
>> End Sub
>>
>> --
>> Brendan Reynolds (MVP)
>>
>> "Jim in Northwest" <JiminNorthwest@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
>> message news:08306A97-A5D9-443D-A349-C66EE676222E@xxxxxxxxxxxxxxxx
>> > Thanks for your suggestions!
>> > I am using the following statement:
>> > Set rs1 = dbs.OpenRecordset _
>> > ("SELECT MyQuery.* FROM MyQuery ORDER BY MyQuery.Field1")
>> >
>> > The error message I got is
>> > Runtime error 3131 - Syntex error in FORM Clause
>> >
>> > If I replace the query with a tablename, everthing works fine. I am
>> > using
>> > Access 2002 and MyQuery may be a cross-tab query.
>> > Any more suggestions?
>> > Thanks!
>> >
>> > --
>> > Jim in Northwest
>> >
>> >
>> > "Brendan Reynolds" wrote:
>> >
>> >> Provided that the query 'MyQueryName' includes the field 'field1' in
>> >> its
>> >> SELECT clause, it should work, yes.
>> >>
>> >> If you still can't get it to work, try posting the SQL for the
>> >> 'MyQueryName'
>> >> query.
>> >>
>> >> --
>> >> Brendan Reynolds (MVP)
>> >>
>> >>
>> >> "Jim in Northwest" <JiminNorthwest@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
>> >> message news:1CE21964-CB28-4BF3-9E95-35BDAE2A5E59@xxxxxxxxxxxxxxxx
>> >> > We can do the following to open a query as recordset:
>> >> >
>> >> > Set rs = dbs.OpenRecordset("MyQueryName")
>> >> >
>> >> > Can we do the following to sort a field inside the query?
>> >> >
>> >> > Set rs = dbs.OpenRecordset("select * from MyQueryName order by
>> >> > field1")
>> >> >
>> >> > I didn't have mush luck for the last sort SQL statement to open the
>> >> > recordset. Any suggestion?
>> >> > Thanks
>> >> > --
>> >> > Jim in Northwest
>> >>
>> >>
>> >>
>>
>>
>>


.

Relevant Pages

  • Re: Calendar Form
    ... If it's the name of a query, I need to see the SQL of that query. ... My Calendar form opens, and I can add information into it, as well as ... The fact that the previous line (Dim db As DAO.Database) doesn't ...
    (microsoft.public.access.forms)
  • Re: Calendar Form
    ... If it's the name of a query, I need to see the SQL of that query. ... To see the SQL of a query, open the query in Design view, then select SQL View from the View menu. ... Is there perhaps a way that I can insert a combo box on my calendar form for "CompanyName", in which I can select a company from the list, and then add/view information on the calendar for that specific company? ... The fact that the previous line (Dim db As DAO.Database) doesn't cause problems implies that the DAO library is properly referenced. ...
    (microsoft.public.access.forms)
  • Re: Ordering a filtered proximity search
    ... >>> search variable, filtering it, and ordering it at the same time. ... >>> appended query approach. ... if p1 is empty then your SQL syntax is messed up. ... Dim SearchTerm1 ...
    (microsoft.public.sqlserver.fulltext)
  • Re: Building SQL statement in VBA. Getting 3131 Runtime Error in FROM clause :@
    ... Number of characters in a cell in the query design grid 1,024 ... Since the SQL parser mentions an error in the FROM clause, ... The only problem I can see is the SQL string is very long. ... Dim qdf As QueryDef ...
    (microsoft.public.access.queries)
  • Re: SQL help
    ... query and output the record count to another text box on the same form ... Dim strTotalships as string ... Can you help with this portion of the SQL statement? ...
    (comp.databases.ms-access)