VB Code -> Query

[Rus925]

I have VB code for a query, but I don't know how to turn it into an actual
query. Any advice? Thanks!

Here's the basic code (from
http://www.microsoft.com/office/com...c98740-7291-4c87-999b-e25f0b53bfeb&sloc=en-us):

Dim str AS string
str= " SELECT "

if check1 then str=str & " field1,"
if check2 then str = str & " field2,"
....

' remove the extra coma ( I assume at least ONE field will be selected)
str=Left(str, len(str)-1)

'complete the statement
str= str & " FROM tableName ... "

'use the string a record source (form/report) or row source (combo box,
list box)

 

[pietlinden]

I have VB code for a query, but I don't know how to turn it into an actual
query.  Any advice?  Thanks!  

```
Create a querydef object, (make sure it has a name), assign the .SQL
property of the object to your SQL statement/function that returns the
SQL Statement, and then append it to the querydefs collection.

Okay, now tell WHY you need this? Are you building a filter on the
fly for your report? There's an answer for that at Access Web.
www.mvps.org/access Search for it.

Do you intend to just build a query where you can modify the SQL on
the fly? (Running out of ideas...)

 

[Rus925]

Thanks! Exactly: I am using VB to dynamically generate SQL code based on the
user's input in a form (which will also ask for the parameters of the query).
Basically, I want the user to quickly be able to choose which fields he/she
wants visible in the report and by which field he/she wants to sort from the
form, along with what he/she wants to search for.

How do I create a querydef object? All I have right now is a module with
the code.

 

[Douglas J. Steele]

Dim qdfNew As DAO.QueryDef
Dim strSQL As String

strSQL = "SELECT ...."
Set qdfNew = CurrentDb.CreateQueryDef("QueryName", strSQL)