Using a form selection as a query "Field"

[theziff]

Hello,

I've inherited a database that has a table with a large number of Yes/No fields. Apart from normalizing the database, I was wondering if the following was possible:

ContractTable has Yes/No Fields for rights granted for each order (television, dvd, internet etc).

Using a form, the user selects the specific right they'd like to run a report on - selecting this right from a drop-down menu, this is labelled "RightsCombo21" on the form.

When the button is clicked, the right selected is now the "Field" section of the query.

The SQL query right now looks like this (shows all rights sold for the territory):

WHERE ((([Contract Table].[Contract Cancelled])=False) AND (([Contract Table].[Contract End Date])>Now()) AND (([Contract Table].[Contract Type])="License Agreement") AND ((ContractIDAndCountry.CountryName)=[Forms]![TerritorySpecificRightsChooser]![TerritoryCombo7]));

I'd like to add something to this WHERE statement:

AND (([Contract Table].RightsCombo21)=True)

Not sure if it's a syntax issue, or I'm attempting something that isn't possible - I'm open to any solutions, from using a TempVar or a VB solution.

Thanks in advance.

 

[theziff]

Just a follow up - I'm looking to use the combobox selection as the "Field" in the query, rather than the criteria - I haven't been able to find anything online in regards to doing this, which is why I have the suspicion it might be problematic.

Thanks again.

 

[MLUCKHAM]

You need to build the SQL up in code really. If you need to save the query you can use a queryDef to create a permanent copy. To use the value as field name is not possible directly, but is simple if you build up your string in VBA. Once the SQL is built you can then either attach it to a form or report or save it as a queryDef.

I would use a SQL template with placeholders and then use the Replace command to update the missing values.

 

[theziff]

Thanks for the reply.

So if I build the SQL statement in VBA, what syntax/format should I be using to refer to the combo box in a form - in my database it's called

[Forms]![TerritorySpecificRightsChooser]![Combo24]

Sorry to be asking such a basic question - I've been checking around online and finding a general example for this specific problem is proving to be problematic: probably because I'm not exactly sure what the terminology is.

Thanks again.

 

[MLUCKHAM]

An example for you.

At the top of your form you could declare a private constant which would be your SQL statement that you want to modify: -

Private Const MySQLStatement = "SELECT SUM(@FieldName) FROM MyTable"

The @FieldName is a placeholder for the field that you are going to populate from your drop down list.

Now, either on a button or on the onChange event of the drop down list you need code like this: -

Dim sql as string

sql = replace(MySQLStatement,"@FieldName",[Combo24])

Your sql statement will not be complete and you could use it in a recordset if you like: -

dim rs as recordset

set rs = currentdb.openRecordset(sql)

or you could set it to the record source of a form: -

me.RecordSource = sql
me.Requery

or you would create a permanent query by using the queryDef object model. This way you can create a permanent query, but I would have thought your queries would be temporary.

I would write the sql query first, then put your placeholders in the place that you want the field name to be replaced with by the drop down box. Then copy the sql into a constant and your are away...

Other things to consider. You need to test that the drop down box us not null before you build your sql or you will get an error: -

if not isnull([Combo24]) then

'put your code here

end if

Finally, you only need to put the full form reference if your code is outside the form in question. Have a play with it and see how it comes out.