use combobox as fieldname in query (without using VBA)

[easyit]

Hi,

I have a form with a combobox. The combobox third column contains a textvalue that I would like to use as a fieldname in a query. Is this possible?

I use this for returning the combovalue

(Eval('[Forms]![frm_Main]![cmbCustomer].column(2)'))

The table name is P.

I tried

Expr1: [P].[(Eval('[Forms]![frm_Main]![cmbCustomer].column(2)'))]

and:

Expr1: [P].[" & (Eval('[Forms]![frm_Main]![cmbCustomer].column(2)')) & "]

Is my syntax wrong or is it not possible like this? I do not want to use VBA due to maintenance of the queries.


EasyIT

"Do you think that’s air you're breathing?

 

[SkipVought]

forum702?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[easyit]

hmmm, not sure about that...

EasyIT

"Do you think that’s air you're breathing?

 

[easyit]

It is a query, isn't it?


EasyIT

"Do you think that’s air you're breathing?

 

[PHV]

For me, Eval is an Access.Application method, not an JetSQL function.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[easyit]

Even so, when used as a criteria it works. Or even if used as field it will return the string that is selected in the combobox. So it seems (almost) possible to use the string as a field in a select query as well.





EasyIT

"Do you think that’s air you're breathing?

 

[easyit]

Maybe my question isn't clear enough; the values in the combobox are the field names of a table. Instead of writing the SQL in VBA I would like to get make the field name in the select query variable.

Is it possible at all?

EasyIT

"Do you think that’s air you're breathing?

 

[Golom]

... I would like to get make the field name in the select query variable

'Fraid not. The only things that you can supply to a query are data values. You cannot supply SQL predicates, operators, table names, field names or any other thing that is part of the SQL syntax. The reason is that a stored query is pre-compiled, lacking only the data values that are determined when you run it.

If you need to do this then you will have to resort to building the SQL that you want using VBA and then set that as the SQL for the query using a QueryDef object.

 

[easyit]

OK, if thats the case I'll switch to VBA.

Thank you all for the responses!



EasyIT

"Do you think that’s air you're breathing?

 

[dhookom]

If you have a limited number of columns, you can create a Row Source like:
[tt]
1 City
2 State
3 Address
4 First Name
5 Last Name
[/tt]
You can then use an expression in the query like:

Choose([Forms]![frm_Main]![cmbCustomer], [City], [State], [Street], [FName], [LName]) As SelectedField

Duane
Hook'D on Access
MS Access MVP

 

[easyit]

Hi Duane,

Thank you for mentioning this possibility. I have already implemented the VBA option, but will experiment with thsi as well.

regards,

Maarten


EasyIT

"Do you think that’s air you're breathing?