Parameter For Table Field

[bigmerf]

I have a table (Table A) with several fields on it (Field1, Field2, Field3.....). What I would like to do is create a query with a parameter where the user can select which field they would like to see on the query. For instance, I have created a parameter that asks

[Please Enter Field:]

When the user types in "Field2" I want my parameter field in the query to use that field from the table.

Is this possible?

 

[fneily]

There's various ways to do that. I'd create a form with an option group. Each option would be a certain field. When the user selects which field they would like to run the query on, code on the AfterUpdate event of the option group would run with a Select Case statement creating the SQL, then run the query.

 

[bigmerf]

Okay, do you have an example of this?

I can create the form, option box and whatnot. I guess where I'm confused is how to setup the case statement. Can't I just use the query builder to design a query where a parameter is setup to pull the specific field selected?

 

[PWise]

try this for the query

SELECT Students.StudentID,  IIf([please enter field]=1,[StudentFirstName], IIf([please enter field]=2,[FamilyLastName],null) ) AS Expr1
FROM Students where [please enter field] between 1 and 2

change field and table namemes

 

[bigmerf]

Okay, I could see that working, but what if my table has 35 fields on it? I would have to write a nested if statement to account for all 35 options. Isn't there a way to just use the query builder to pull in the string value entered by the user to be used as the field name for the table?

FieldName: "[" & [Enter Field Name:] & "]"

or something like that? Or, is using SQL the best route to go. I'd hate to write a HUGE nested SQL statement for this. If this is my only option, the so be it.

Thanks!

 

[PWise]

So you want to reinvent the wheel -bulid your own query builder

 

[Pampers]

Hi WLTC,
I think I would go with fneily's suggestion, write an sql-statement that takes what is selected by the user to build a where clause (all in vba of course). But instead of using an option group (if your table has 35 fields that is a bit unpractical), use an combobox that reads your table fiels. The user can now choose a field from the combo (from this you can create the WHERE part of the query), gives a value (WHERE FIELDNAME = VALUE) and you have your query in a limited amount of code... Then execute the query - (currentdb.execute(strSql) or something like that



Pampers
Keeping it simple can be complicated

 

[JoeAtWork]

Are you comfortable with VBA code? That is the only realistic option, in my opinion.

 

[bigmerf]

Yes am I familiar/comfortable with VBA. I'm going to use a makeshift query & form to get the results I want. Thanks all for your opinion/input!