How to use a 'Combo box' value as a 'Colmumn name' in a query?

[nikhilthemacho]

Hi all,
I am using Access 2007. Am a beginner.
I want to use the option selected in a combo box as a colum name in a select query. Following are the details.

Table name: Table_1
Column names: C_1, C_2, C_3, C_4
Form name: Form1
Text box name: Tb_1
Combo box name: Cb_1

The user enters a phrase to be searched for in 'Tb_1'. The user selects the column name (say 'C_2') from the combo box (Cb_1 in this case). Hence value of Cb_1 is C_2 now. A wildcard search (that is a select query) is to be performed on the selected column ('C_2' in this case) for the phrase entered.

I am using the following query:
Select Table_1.*
from
Table_1
where
Table_1.[Forms]![Form1]![Cb_1]
Like "*"& [Forms]![Form1]![Tb_1] &"*"
;

But, I am not getting desired results.
As soon as I run the query, I get a box asking for value of Table_1.[Forms]![Form1]![Cb_1]

And even after entering a correct column name in the box, it returns ZERO results.

In short, I want to use the option selected in the combo box as a column name in a select query.

Can somebody please help.

 

[vbaInet]

A field or table name cannot be concatenated to an SQL string from within the query designer. If you want to fire off that sql you will need to do it in VBA.

 

[nikhilthemacho]

Thanks for replying vbaInet. But can you please tell me how to do that? If possible, can you elaborate where and what exact code should I put in VBA ? (considering above mentioned names of tables, forms, etc.)

 

[vbaInet]

First of all your field names suggests that your table is not normalised. Why the C_X as field names?

Dim strSQL as String
 
strSQL = "[B]SELECT * from [/B][B]Table_1 WHERE " & Me.Cb_1 &[/B][B] " [/B][B]LIKE '*" & Me.Tb_1 & "*';"[/B]
 
.... do whatever with the query now...