Convert string to field name

Question

Hey there,

I'm having 2 different problems wich are essentially the same - I want Access to use a string that was selected through a combo box as the field name for either a graph or a query.

In my query (design view) I have entered in field the following statement: [forms]![formname]![cmb_1].Value

To update a graph I'm trying to use this code:

Private Sub cmd_graph_Click()

Dim x_axis As String

Dim y_axis As String

x_axis = Me.cmb_x.Value

y_axis = Me.cmb_y.Value

Me.ctrl_graph.RowSource = "SELECT x_axis As X , y_axis As Y FROM tablename;"

Me.Refresh

End Sub

Both are not working - all I get is the name of my fields either as a query result or in a graph. Do I have to convert the variable somehow?

Thanks in advance!

Octavio

Answer 1

Access SQL doesn't support the use of variables in the names of tables and fields.

Answer 2

Thanks a lot, it's working much better now. I'll need to read more about the graph control to edit the graph type but it's working for now.

Does this apply for the query as well? Because there if I try to enter it that way "[" & [forms]![formname]![comboname].Value & "]" it returns a syntax error.

Is there any way to use variables in the SQL code? I've tried using DECLARE but that's not doing it in the query design.

Answer 3

Change the line

Me.ctrl_graph.RowSource = "SELECT x_axis As X , y_axis As Y FROM tablename;"

to

Me.ctrl_graph.RowSource = "SELECT [" & x_axis & "] As X, [" & y_axis & "] As Y FROM tablename;"