Concatenating control's value to a query

[jtertin]

I have a combo box on a form with values 1-5. I want the value of
this combo box to be used in a form later on, but I need it
concatenated into a query as this simplified example shows:

SELECT ...
FROM ...
WHERE Line&'[Forms]![MyForm]![MyComboBox]'&=True

You can see that I am trying to concatenate the current value of the
combo box to the word LINE (i.e. Line1, Line2, Line3...) which refers
to a boolean column in my database.

 

[Douglas J. Steele]

Sorry, you can't do that in a query.

You could, however, dynamically change the SQL associated with the query and
then write it.

Dim qdfCurr As DAO.QueryDef
Dim strSQL As String

strSQL = "...."
Set qdfCurr = CurrentDb().QueryDefs("NameOfQuery")
qdfCurr.SQL = strSQL

 

[jtertin]

So, if I understand this correctly, the outcome of this will be a new
query called "NameOfQuery" in this case. Will this be dynamically
updating the query that appears in the list of queries in Access?

 

[jtertin]

What an AMAZING method of creating dynamic queries! You can bet I am
going to be using this a LOT from now on.

Thank you VERY much for the advice!!! Works GREAT!