Field name within a field (macro substitution)

[Lobster1071]

I’m attempting to obtain a value from a field within a query. However, the field will vary based on selected info from a form. Yes, I know this is vague, but I’ll given an example below. This may not even be the best way to do this, so any suggestions would be appreciated.

What would really solve my problem is trying to insert a field name contained within a field. I know FoxPro had a “&” command which is called a “macro substitution”. This would resolve my issue I believe, but Access doesn’t seem to have an equivalent command.

Example:
A form has 3 selections, all with fixed values that a user must choose (either via a radio button, or combo box)
Form1.text1 (Choices are Blue or Red)
Form1.text2 (Choices are AB or CD)
Form3.text3 (Choices are Cat or Dog)

Based on the info from the form, that is the column I would like displayed in the query. Basically, one field in the query will be a variable based on the form selections.
The query (and table of course) will have the fields 8 different fields which will be “Blue_AB_Cat”, “Blue_AB_Dog”, “Blue_CD_CAT”, “Blue_CD_Dog”, etc…

I simply would like to have a column in the query that shows the data based on the selection in the form.

I know this is incorrect, but if one column in the query could say…

[Form1.text1]&”_”& [Form2.text2]&”_”& [Form3.text3]

…that would be essentially what I am looking for. The three choices on the form becomes the actual field name.

Thanks

 

[DCrake]

Simple Software Solutions

There is a way to do this, as follows.

Within your form use the radar buttons/etc to generate your psuedo field name as decribed in your thread. The use the queryDef.SQL command to change the sql statement for the query. I assume that there will be a template query and you know where in you query you would like the field to appear. So in short you will have a before and after string simply insert the field name between them and save the new sql to the query.

CodeMaster: