Can SQL statement using a query rather than a table?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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,

Yes, you can use an SQL expression string in the OpenRecordset argument
(at least definitely so with DAO). I don't know why it didn't work for
you, typo maybe? Suggest you make the query in design view, and when
happy it's returning what you expect, revert to SQL view, copy the
expression and paste it in your code.

HTH,
Nikos
 
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.
 
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!
 
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:

It works! I noticed you are using square bracket in your SQL statement.
That works when using query in the SQL statement (ie. using [QueryName] in
the statement). When we are using table name in the SQL statement, the []
seems not required.

Much thanks to your help!
--
Jim in Northwest


Brendan Reynolds said:
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
Click to expand...
 
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 said:
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
Click to expand...
 
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 said:
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 said:
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 said:
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


:

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)


message 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
Click to expand...
Click to expand...
Click to expand...
 
Thanks a million, Brendan!
Yes, I did have a special character (didn't know until now) in my query
name. That is the hyphen, such as "myquery-abc".
Many thanks.

--
Jim in Northwest


Brendan Reynolds said:
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 said:
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 said:
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)

message 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


:

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)


message 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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top