Field Selection in SQL from form.

[SikSlk]

Hi

I cant find this for the life of me... this is the second day trying to figure it out.
Anyway enough crying

Question:
Either using a SQL statement or query builder, how is it possible to make the field selection come from a form, ie a combo box, text box etc. or a variable?

Reason:
Im trying to make a dynamic searching form which will allow the searching from customers via a few of many columns which reside in 4 different tables also. Using a cascading combo box system to eventually narrow down the correct one. The above is my first hurdle and I think once I get past this brick wall I will be back on my way.

 

[CraigDolphin]

Are you perchance talking about creating a query that uses a parameter to limit the results? (the parameter being supplied by a control on the form in this case).

If so, then, in the criteria line of your query, simply put something like:
Forms!YourFormName!YourComboName

Do a search on parameter queries and referencing form controls to learn more.

 

[pbaldy]

You can't find it because it can't be done. If you want the fields selected in the query (SELECT PickAField FROM Tablename) to be dynamic, you'll need to build the SQL in VBA. Searching for "dynamic SQL" should turn up threads on the topic.

 

[pbaldy]

Unless I misunderstood, in which case Craig's answer is correct.

 

[CraigDolphin]

Unless I misunderstood, in which case Paul's answer is correct.

 

[SikSlk]

Hi, Thanks for the replys. I think im going to go fourth and search the dynamic SQL (should have tried that one, im sure iv tried everything else)

Sorry to clarify (i think) I need to insert the searchable feild into a SQL query. the feild will come from a combo box primarily but if it needs to be a variable that I can make it do that also. I dont know much about SQL but figured it could be able to do this.

Thankyou again... will post back when I have beaten it.

Do or Do Not, there is no try - YODA

 

[SikSlk]

ok, sorry wanted to repost something that made a lil more, um.... English? lol.

SELECT DISTINCT Customer_advsearch.[cbofeild1] FROM Customer_advsearch;

Ok, im trying to make the cbofeild1 link to a form... a combo box on a form. this possible?
This added question help or confuse more?

 

[CraigDolphin]

I misunderstood. Follow Paul's advice and search on how to make and use dynamic SQL statements from VBA.

 

[KeithG]

I've done something similar to this to create an ODBC pass through query to an IBM DB2 database. I would suggest using a listbox instead of a combobox.

 

[SikSlk]

Hi thanks for the heads up, Im still having trouble finding anything referencing what im trying to do.

I can make a combo box and then make it copy the selected field to a variable or a text box to then be used in the SQL. I just cant find anything other then dynamic tables?

Iv tried various brackets, commers and etc to hopefully find it accidentally but no luck thus far. Im back to google some more, thankyou for you comments which indicate its possible and im not crazy.

 

[pbaldy]

I found numerous threads on this site with a search. Also, here's a "how to" that includes a sample db that populates a subform with dynamically build SQL:

http://www.mdbmakers.com/forums/showthread.php?t=4895