Problem passing field name parameter to a query

[novena]

I need to analyze the frequency with which paired true-true combinations of 50+ true/false variables occur together. To this end, I have a table of test results stored in an Access database. I'm trying to build a query that will accept a parameter of field name, so that I can control which two fields are being evaluated as 'true-true' and counted. Once I have the query working I'll code some VB to vary the fields being evaluated and pass the results to Excel for analysis.

The weird thing is, when I hard-code the field names into the query, they work fine. But when I change the field names to be input parameters, the query ends up counting all records instead of only the 'true-true' combinations.

Here's the SQL:

SELECT Count(TST_Combinations.[c1]) AS CountOf1
FROM TST_Combinations
WHERE (((TST_Combinations.[c1])=True And (TST_Combinations.[c2])=True));

Any suggestions? I reeeeally don't want to manually create the 1250 or so queries it takes to do this analysis!

Thanks.

 

[dhookom]

I would suggest that your table structure isn't normalized. There is a demo survey application at

http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane

the might work better for you.

If you want to keep your table structure as is, I would create a form with two combo box that have a row source type of "Field List". Name them cboField1 and cboField2. You can then run code the changes the SQL of a saved query:
Dim strSQL as String
strSQL = "SELECT Count(*) as CountOf " & _
"FROM TST_Combinations " & _
"WHERE [" & Me.cboField1 & "] = True AND [" & _
Me.cboField2 & "] = True;"
Currentdb.QueryDefs("qselMyQUery&quot.SQL = strSQL
This code requires that you have a reference to the DAO library.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[JonFer]

Two suggestions.

First - you can simplify the Where clause like this if the fields are the Yes/No type:

"WHERE " & Me.cboField1 & " AND " & Me.cboField2 & ";"

Second, you could use a multi-select list box for the second control so you can get multiple query results at once. Use the For Each/Next statement to loop through each field selected in the list box. You can change the SQL to do an Insert, add two columns which name the fields being checked, and put the results into a table in your database.