Pass Form value to Report Record Source

[PLCBeetle]

Hello...

I am trying to pass a column name from a form to a report query record source without any luck.
The Report_Date data is coming thru into the form detail but that is all


Form name: frm_reports
Form text box: comment_search This text box contains the name of the column in the Daily_Data table I want.

Query I am trying to use.
Report Record Source: SELECT Daily_Data.Report_Date, Daily_Data.[Forms]![frm_reports]![comment_search] FROM Daily_Data;

Please advise. Thank you for any and all help. I am frustrated and Googled out.

 

[sxschech]

Are you saying that the form will select the column that you want displayed?
First Col is always date and the second col can be what ever col the user selects, so today could be Comment and tomorrow could be Product_Name?

--OR--

Is it that you want to filter the data by a value in comment_search?
Where the end result would be something like: SELECT Report_Date, [OtherField{s}] FROM Daily_Data WHERE comment_search = 'Received all Data' <== that would be the value passed from the form.

 

[dhookom]

I would use a little VBA code to change the SQL property of the query your report has as its record source faq701-7433.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[PLCBeetle]

Good morning...

I am trying to populate a Report with only 2 fields. This query works fine as the Record Source of the report:

SELECT Daily_Data.Report_Date, Daily_Data.Water_Comments AS Display_Data FROM Daily_Data;



BUT, what I really need is something like this as the Record Source of the Report:

SELECT Daily_Data.Report_Date, Daily_Data.(" & Forms!frm_reports!comment_search & ") AS Display_Data FROM Daily_Data;

I need to substitute the column named Water_Comments for the contents of a Combo Box on a form which includes other column names.


Thank you so much for any assistance. Happy Holidays.

 

[dhookom]

PLCBeetle,
Did you at least attempt to implement my suggestion?

An alternative is to use a combo box [cboField] on your form with a row source the has numbers and field names from Daily_Data like:
[pre]1 Water_Comments
2 Lead
3 PH
4 Alkalinity
5 Total_Chlorine[/pre]

The control is bound to the number column.

Then in your query you can use the Choose() function like:
[pre]Choose(Forms!frm_reports!cboField, [Water_Comments], [Lead], [PH], [Alkalinity],[Total_Chlorine]) AS Display_Data[/pre]


Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016