Define multiple search criteria from one form but only use selected

[Ru55ell]

Hi
I have written a form that has 4 drop down selection boxes on it, with check boxes next to them. When a user selects something from the drop down box the check box is automatically selected. I want to perfom a query and based on what has been selected from the drop down boxes use this information as the criteria. If a user has only selected a supplier name then the query should return all products from that supplier, but if a user selects a supplier name and colour then only products from that supplier that are that colour should be returned. (if that makes any sense?) I can get the query to do what I want on an individual criteria basis, but it is the ability to test whether a check box has been ticked and then apply that criteria that is causing me a headache. Any help on this would be grately appreciated.

[MMcCarthy]

I don't think you will be able to do this with just a query. I would use a VBA routine to build the query into a string. Something like ....

 
Dim strSQL as String
Dim start as Boolean
 
    strSQL = "SELECT * FROM MyQuery "
    start = False
 
    If Me.Checkbox1 = True Then
        strSQL = "WHERE Supplier='" & Me.combobox1
        start = True
    End If
 
     If Me.Checkbox2 = True Then
        If start = True Then
            strSQL = "AND City='" & Me.combobox2
        Else    
            strSQL = "WHERE City='" & Me.combobox2
            start = True
        End If
    End If   
 
    DoCmd,RunSQL strSQL
 
 

This is just a rough idea to get you started. How you implement the query will depend on your requirements.

Mary

[Ru55ell]

Thanks for that I've been away for a few days but will give it a go.