Basic Query - long-winded explanation!

[GP]

Can anybody out there assist - Pleeease!!!

I am trying to create a basic query and just can't seem to get it right. I'll try my best to explain what I need....

The database is used to track parts. (Part 1, Part 2, Part 3.. etc etc - each part is unique, and has it's own record). There are four types of car (A,B,C & D) that these parts can be fitted to. eg Part 1 can be fitted to car types A, B, & D. Part 2 can be fitted to car types C & D. Part 3 can be fitted to car type A etc etc. For each of the four types of car, there is a 'Yes/No' field assigned. My problem arises as I'm trying to create a query, that gets it's query criteria from a form, which consists of four check boxes A,B,C & D. If, for example, only check box D is ticked, and the query is run, the query will retrieve parts 1 & 2. If only C is checked, only part 2 will be retrieved. If A & D is checked, it will retrieve all 3 parts. Can anybody tell me how to achieve this?

Thanx in advance.
GP
(Sorry for the epic-sized explanation!!)

 

[Rich_Lovina]

Are you using a different criteria line for each yes/no box to achieve a result covering ALL options?

More specifically, what must the query result show.

 

[ott]

This will dynamically set the SQL statement of your query (in this example called "MyQuery") based on the selection of check boxes on your form (chkA, chkB, chkC and chkD):

Private Sub BuildQuery()

Dim dbs As Database
Dim qdf As QueryDef
Dim strWhere As String
Dim blnPart(1 To 3) As Boolean
Dim i As Integer

Set dbs = CurrentDb()
Set qdf = dbs.QueryDefs("MyQuery")

If chkA Then 'Parts 1, 3
blnPart(1) = True
blnPart(3) = True
End If

If chkB Then 'Parts 1
blnPart(1) = True
End If

If chkC Then 'Parts 2
blnPart(2) = True
End If

If chkD Then 'Parts 1, 2,
blnPart(1) = True
blnPart(2) = True
End If

strWhere = ""
For i = 1 To 3
If blnPart(i) Then strWhere = strWhere & "PartTable.PartNo = " & i & " OR "
Next i

strWhere = Left(strWhere, Len(strWhere) - 4)

qdf.SQL = "SELECT PartTable.* FROM PartTable WHERE " & strWhere & ";"

DoCmd.OpenQuery qdf.Name

Set qdf = Nothing
Set dbs = Nothing

End Sub

 

[GP]

Thanx Rich/ott,

Rich: The query form consists of 4 check boxes (chkA, chkB, chkC & chkD) and a "RunQuery" button. When run, the query should bring back all records that have whatever check boxes have been ticked as "Yes" in their record.

For example: If chkA is ticked (and the query is run), then the query should bring back all records that have the field 'A' as Yes in the data table. If chkA & chkB are both ticked, the query should bring back any records that have fields 'A' AND 'B' as Yes in the data table. If all four 'chk' boxes are ticked, the query should only bring back recoerds that have have Yes in all four corresponding Fields in the Data table.

ott: Thanks for your explanantion, however, I'm a complete 'Access' novice and don't really know how to use this. Any chance you could explain a bit more?

)

 

[GP]

I meant