Parse a txt field to a runSQL command

[paulw4]

I have a form that enables the end-user to "build" a SQL query. They can select up to 11 fields, use multiple Select conditions, and 4 sort levels. As they build the query it is put together in a txt field. The next step was to run the SQL, which I do from a command button, the code is as follow.

dim qryOutput as string

qryOutput = me.txtQry.Value ( THE TEXT BOX on the FORM)

docmd.runSQL "qryOutput" (Have tried various forms of this, used brackets and or commas)

I get an error saying the runSQL expects "SELECT" or "INSERT", ETC...

How can I run the built SQL statement?

Thanks for your help!

PW

[JKing]

The RunSQL command is only for action queries. Deletes, select into, insert into etc.

If you want to create queries on the fly I suggest doing the following.

Step 1: Create a query and save it. It doesn't matter what you include in it as long as you save it. Access doesn't allow you to save a blank query so we're are doing this to get around that. You can call it whatever you want but for this example we will call it qryMyQuery

Step 2: Through code declare a querydef object and change the SQL of the query to reflect your built SQL.

Step 3: Open the altered query.

[code=vb]
Dim db as DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String

Set db = CurrentDb
Set qdf = db.QueryDefs("q ryMyQuery")

strSQL = "SELECT * FROM yourTable"
qdf.SQL = strSQL

DoCmd.OpenQuery ("qryMyQuery ")

Set qdf = nothing
Set db = nothing
[/code]

[paulw4]

Still not able to take the data in the txt field and have it run as an SQL statement.

Below is the data in the text field txtqry on the form "test"
[code=sql]
SELECT [ContactFirstNam e], [ContactLastName], [Delivery City] FROM Customers WHERE [ContactLastName] > "D" ORDER BY [Delivery City], [ContactLastName]
[/code]
Thanks to your help I have the following code.

The "qrytest1" saved select query outputs zip code only....
[code=vb]
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String
Set db = CurrentDb
Set qdf = db.QueryDefs("q rytest1")
strSQL = Me.txtQry.Value (Have also tried forms!test.txtq ry)
DoCmd.OpenQuery ("qrytest1")
[/code]
When this run I get the zip code only output, not what the built select query is.

The intent is that the qrytest1 will run select statement from the txtqry text box.

Again, thank for the help
PW

[JKing]

You missed a key line in my previous post.

You're missing this line:
[code=vb]
qdf.SQL = strSQL
[/code]