relative field names in query-dynamically refer to fields

[pedro1]

Access 97: is there a way to dynamically reference fields in queries/reports using controls? That is, if I want a field in a query to be "Amt 1" this time and "Amt 2" or 3 or whatever after this, can I have a control prompt me for the field name and give me the field contents instead of a column of "Amt 1"?

Thanks!

Ken

 

[grnzbra]

Are you trying to create a query in which you can change the name of a field each time you run the query?

 

[pedro1]

Yes. For example, I'm often appending data from several fields into a table of few fields. A lot like a reverse cross-tab query. So, I would like to be prompted for the field to append in a dynamic fashion rather than manually changing the field each time. Also, I wonder if I can relatively reference a table name in a control.

 

[pedro1]

Yes. Often I want to append data from many columns in one table to one column in another...like a reverse cross-tab query.

 

[grnzbra]

You could write some code that does a create object and create the query each time it is run where the name of the field in the sql statement is a variable. Do you want to be prompted for the field name and then have it run or do you want to pick up the name of the field from a table automatically (let's say depending on what field you find data in)?

 

[pedro1]

Actually, picking the field from a table of fields would work very well. E.g. a table with a record for each field name I want to select.