Using Multiple List Boxes To Query The Database

[DAPOOLE]

Using Access is it possible to query the database using multiple list boxes where the user can specify the AND or an OR clause?

For example, I have 2 list boes which I want the user to use to build up their query however sometimes they will want to use data from both boxes with an AND while other times they will only want to query with an OR. In the code below it is using OR ( on the line "WHERE " & strCriteria & "OR " & strCriteria1 & ";" ), however is it possible to code this to be user selected? i.e. the user specifies from the form if this value there is an AND or an OR.

Private Sub Command17_Click()

On Error GoTo Err_Command17_Click

   Dim db As DAO.Database
   Dim qdf As DAO.QueryDef
   
   Dim varItem As Variant
   Dim strCriteria As String
   
   Dim varItem1 As Variant
   Dim strCriteria1 As String
   
   Dim strSQL As String
   Set db = CurrentDb()
   Set qdf = db.QueryDefs("qryMultiSelect")
   
   
   If Me!lstLicenceType.ItemsSelected.Count > 0 Then
      For Each varItem In Me!lstLicenceType.ItemsSelected
         strCriteria = strCriteria & "dbo_client.type = " & Chr(34) _
                       & Me!lstLicenceType.ItemData(varItem) & Chr(34) & "OR "
      Next varItem
      strCriteria = Left(strCriteria, Len(strCriteria) - 3)
   Else
      strCriteria = "dbo_client.type Like '*'"
   End If
   
   
   If Me!lstCustType.ItemsSelected.Count > 0 Then
      For Each varItem1 In Me!lstCustType.ItemsSelected
         strCriteria1 = strCriteria1 & "dbo_client.cust_type = " & Chr(34) _
                       & Me!lstCustType.ItemData(varItem1) & Chr(34) & "OR "
      Next varItem1
      strCriteria1 = Left(strCriteria1, Len(strCriteria1) - 3)
   Else
      strCriteria1 = "dbo_client.cust_type Like '*'"
   End If
   
   
   strSQL = "SELECT clientno FROM dbo_client " & _
            "WHERE " & strCriteria & "OR " & strCriteria1 & ";"
            
   qdf.SQL = strSQL
   DoCmd.OpenQuery "qryMultiSelect"
   Set db = Nothing
   Set qdf = Nothing


Exit_Command17_Click:
    Exit Sub

Err_Command17_Click:
    MsgBox Err.Description
    Resume Exit_Command17_Click
    
End Sub

Thanks in advance.

 

[pbaldy]

Sure; give them a combo box to choose, and change this:

& " OR "

to

& " " & Me.ComboName & " "

 

[DAPOOLE]

Sure; give them a combo box to choose, and change this:

& " OR "

to

& " " & Me.ComboName & " "

Brilliant. Thank you so much.

 

[pbaldy]

No problemo, glad it helped.