Multi select Query

[BRANDON99]

Hi

I have a table “Customers”, and 6 of the fields are for the products (Say A,B,C,D,E,and F) that they stock, these fields are Yes/No. I want to create a query that will select customers depending on the products they stock. I want the criteria for this query to be entered in a form so that users can easily get this data. EG if they want to select customers that stock A and B, they can enter this (via a check box?) and get the info.

Can anybody help please

Thanks

Brandon

 

[jiqjaq]

This is really a question for the Access Queries section.

If you name the checkboxes chkA thru chkF (or some other scheme which ends with those letters, it's much easier to do. Keep in mind that the letter "A" = CHR(65).

Dim strSQL as String          ' SQL string
Dim blnChk(1 to 6) as Boolean ' Values from checkboxes
Dim intNdx as Integer         ' Loop index
Dim strFieldNames(1 to 6) as String

strFieldNames(1) = "ProductA"
strFieldNames(2) = "ProductB"
...
strFieldNames(6) = "ProductF"

strSQL = "Select * From Customers Where "

' store checkbox values and append to SQL statement
For intNdx = 1 to 6 
   blnChk(intNdx) = Me("chk" & CHR(64+intndx)).Value
   If intNdx > 1 Then strSQL = strSQL & " AND "
   strSQL = strSQL & strFieldNames(intNdx) & "= " & blnChk(intNdx)
Next intNdx
[\code]

That should put the SQL for the query in strSQL. Then you can use it.

 

[BRANDON99]

Thanks jiqjaq

I will try this, and let you know how I get on