Text Box Value - to Be Field Name in Reports Recordsource 1

[air1access]

How can I get the value of a text box (on a form) to be the field name in a query for a reports record source - at run time?
The text box value will equal a field name that is in a table that is used in the query to build the reports record source.

I'm trying to build it to where the user does not have to go into design view of the query and manually pick the needed field each time the report is generated....

Any suggestions or examples would be much appreciated..!!!
Thanks in advance..!
air1access

 

[dhookom]

I would use a combo box, list box, or option group for the user to select the field. For instance
[ol 1]
[li]First Name[/li]
[li]Last Name[/li]
[li]City[/li]
[li]Region[/li]
[/ol]
Assuming your form is named [frmRptSelect] and your option group is [grpField] your query would have a column defined as:

SelectedField: Choose(Forms!frmRptSelect!grpField,[FirstName], [LastName], [City], [State])

Your report can then have a text box bound to [SelectedField] showing the value of the field selected by the user.

Duane
Hook'D on Access
MS Access MVP

 

[air1access]

Thanks for the response dhookum..!

I'm not keen to the Choose Function. Never used it before...

I have a "global variable" that is set when the use clicks a command button to run the report...
The variable is set off of a value in a text box... Can I use this variable in the Choose Function..?

I'm trying to figure out how I can use an option group but I'm stumped....!

Thanks for your help..!

 

[air1access]

I tried -
Matrix: Choose(1,getglobal('GBLCurrentMonth'))

Alls it does is put "Sept-2015" for the entire recordset in the Matrix column.

There is a field named "Sept-2015" in one of the tables that is used for the query.
I need the value of that field (Sept-2015) in the query....

 

[dhookom]

I doubt your solution will work. Can you provide any of the information that I guessed at? What don't you understand? Do you have an option group or other control to select a field? What does your query SQL look like?

Duane
Hook'D on Access
MS Access MVP

 

[air1access]

Ok - As I was....!!

I figured it out.
After doing some research - I learned more about the Choose Function....
I applied your suggestion, and BAM..!! It does exactly what I was wanting... A different approach then I was thinking, but none the less - I learned something new..
And it works..

Thank you so much dhookom...!!!
You rock..!!!

air1access