Passing a form value to query...

[PLCBeetle]

Hello...

I have a form named: Reports
On this form there is a text box named: Comment_Search
Contents of the text box is: Water_Comments
The contents of the text box are actual column names in a table.

I am trying, without success, to create a query which will use the text box data as the column name in the SQL Select statement.
Here is an example of what I am trying to do.

SELECT Report_Date, [Forms]![frm_reports]![Comment_Search] AS Display_Data FROM Daily_Data;
SELECT Report_Date, ([Forms]![frm_reports]![Comment_Search]) AS Display_Data FROM Daily_Data;
SELECT Report_Date, ([Forms]![frm_reports]![Comment_Search].value) AS Display_Data FROM Daily_Data;
SELECT Report_Date, ([Forms]![frm_reports]![Comment_Search]![value]) AS Display_Data FROM Daily_Data;

If I use this example, with the column name hard coded, the query works as desired.
SELECT Report_Date, Water_Comments AS Display_Data FROM Daily_Data;

Thank you greatly for your time, assistance and help.

 

[Andrzejek]

So, you expect your user(s) to know all the field names from your Daily_Data table?
And you hope they will spell them correctly? Pretty unstable and questionable, if you ask me.

If you do want to employ that approach, I would give them a list of field names to select from in a drop-down list instead.

And if your Select statement is a string, I would try:[tt]
strSQL = "SELECT Report_Date, " & [blue]Forms!frm_reports!Comment_Search.Value[/blue] & " AS Display_Data FROM Daily_Data"[/tt]

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[PLCBeetle]

Hello...

Great idea on the combx box. Thank you for that, I will do just that.

SELECT Report_Date, " & Forms!frm_reports!Comment_Search.Value & " AS Display_Data FROM Daily_Data

I have copied and pasted the code you provided into the SQL query designer and this is the result I get:



Report_Date Display_Data
12/22/2021 & Forms!frm_reports!Comment_Search.Value &
12/6/2021 & Forms!frm_reports!Comment_Search.Value &
8/30/2021 & Forms!frm_reports!Comment_Search.Value &
8/7/2021 & Forms!frm_reports!Comment_Search.Value &
8/24/2021 & Forms!frm_reports!Comment_Search.Value &

 

[Andrzejek]

That's NOT what I gave you. :-(
What I did give you was how to build a String as a Select statement:

strSQL = "SELECT Report_Date, " & Forms!frm_reports!Comment_Search.Value & " AS Display_Data FROM Daily_Data"
Debug.Print strSQL
MsgBox strSQL

What you do with that String, that's another story. You can (probably...?) pass it to your 'SQL query designer'.
Well, all Select, Update, Insert, Delete statements are just Strings....

I assume if you have a Form, and a text box on this form, you do some VBA coding, right?

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[dhookom]

Your combo box can have a Row Source Type of "Field List" where the Row Source then becomes either a table or query (if you don't want to expose all field names).

You can change the SQL of a saved query using this simple code faq701-7433.

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

 

[PLCBeetle]

VBA coding, yes, a little bit.

Not sure if I should use a report or a form to display the data returned.

Also I am not sure how to use the SQL query string you just provided other than to paste it into the Record Source of the Form or Report.
This I have tried without success as you can see.

I can put the SQL string in VBA but the rest after than confuses me how to link it to the Form or Report.

Sorry for being such a bother and I do appreciate the comments and help so I can learn to do this on my own.

Many thanks

 

[Andrzejek]

That's usually up to your customer / user(s) to decided how they want this data to see / have. But looks like you want to display just 2 columns: Report_Date and Display_Data from Daily_Data table, so even a simple text box / list box / grid on the form would do the trick.

And after you know 'what' to do, you can start figuring out 'how' to do it.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson