Query Def

[weltergon]

Hi,
What is the code to run a select query that has been assigned to a string variable within the current DB?

 

[jrbarnett]

DoCmd.RunSQL stringVariable

However, a select statement won't do anything because it will just retrieve the records. If it is the name of a saved query, use

DoCmd.OpenQuery strQueryname

to open the result set.

John

 

[weltergon]

Well I tried the runSQL statement but I kept getting an error. All I need is to retrieve records....I am not updating or anything like that. Heres the error I get:
a runSQL action requires an argument consisting of a SQL Statement, any ideas as to what i am doing wrong??
Thanks

 

[jrbarnett]

What are you going to do with this data when you have retrieved it?
It is better to let Access retrieve the data necessary for something when it is needed by specific queries, forms, reports etc rather than do it before the object opens or code runs.

John

 

[weltergon]

Well I need to run a query based off of selections made from a list box so this needs to be a sql statement...? I am using this for reporting purposes only and I am trying to build a sql statement....Any help?
Thanks

 

[jrbarnett]

OK, the ultimate aim is to build the report based on the SQL statement.

First step: design your report with its associated query or SQL statement. No problem using the QBE designer and copying the data across, just don't put in restrictions on limiting data from the listbox.
If you run it as is, it should display all data.

Second step: Use your form with the list box to build a Where clause (just the where clause) part of the query.

Third step:

In the form, perhaps on a "Run Report" button:

DoCmd.OpenReport "Reportname", acViewPreview, WhereCondition := strYourWhereclause

More info on building report selection forms in my FAQ here:

http://www.tek-tips.com/faqs.cfm?spid=702&sfid=3619

John