SQL Where clause

[Guest]

I do not know anything about SQL, but I think I need SQL for the type of
query I'm trying to run if this is even possible. Right now I have an input
form where a user selects 5 parameters from drop downs and then can select up
to 9 check boxes. Once they have made their selections they are taken to
another form where each of the 9 checkboxes when selected unlocks a listbox.
The user can then select more than one value from each of the listboxes
unlocked. (Anywhere between 1 & 9 boxes are unlocked at one time) I then
have a button they push which runs a query and would return values based on
what they chose in the listboxes. My main question is: How do I get the
query to recognize all list boxes and all values selected in each listbox?

Thanks!

 

[Guest]

I have done this. It is a challenge, but doable. here are some code samples:
This procedure builds the WHERE condition for a specific list box. You pass
the name of listbox you want it to process as a string:

Private Function BuildWhereCondition(strControl As String) As String
'Set up the WhereCondition Argument for the reports
Dim varItem As Variant
Dim strWhere As String
Dim ctl As Control

Set ctl = Me.Controls(strControl)

Select Case ctl.ItemsSelected.Count
Case 0 'Include All
strWhere = ""
Case 1 'Only One Selected
strWhere = "= '" & _
ctl.ItemData(ctl.ItemsSelected(0)) & "'"
Case Else 'Multiple Selection
strWhere = " IN ("

With ctl
For Each varItem In .ItemsSelected
strWhere = strWhere & "'" & .ItemData(varItem) & "', "
Next varItem
End With
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
End Select

BuildWhereCondition = strWhere

End Function

This function concatenates where conditions from multiple list boxes.
lngSelector is the list box number and is called from a command button
associated with each list box. Each command button has a number associated
with it. This routine determines which list boxes to include based on that
number. It will build the where condition that includes the list boxes
selected.

Private Function FindWhere(lngSelector As Long) As String
Dim strWhere As String

If lngSelector = 1 Then
strWhere = BuildWhereCondition("lstPool")
If Len(strWhere) > 0 Then
strWhere = " AND LaborType2 " & strWhere
End If
End If

If lngSelector <= 2 Then
If (lngSelector < 2 And strWhere = "") Or lngSelector > 1 Then
strWhere = BuildWhereCondition("lstBillNetwork")
If Len(strWhere) > 0 Then
strWhere = " AND actvContractActivity " & strWhere
End If
End If
End If

If lngSelector <= 3 Then
If (lngSelector < 3 And strWhere = "") Or lngSelector > 2 Then
strWhere = BuildWhereCondition("lstActivity")
If Len(strWhere) > 0 Then
strWhere = " AND Activity " & strWhere
End If
End If
End If

If lngSelector <= 4 Then
If (lngSelector < 4 And strWhere = "") Or lngSelector > 3 Then
strWhere = BuildWhereCondition("lstMActivity")
If Len(strWhere) > 0 Then
strWhere = " AND actvParentActivity " & strWhere
End If
End If
End If

If lngSelector <= 5 Then
If (lngSelector < 5 And strWhere = "") Or lngSelector > 4 Then
strWhere = BuildWhereCondition("lstBillProdOffering")
If Len(strWhere) > 0 Then
strWhere = " AND BillableProductOffering " & _
strWhere
End If
End If
End If

FindWhere = strWhere

End Function

And here is how I manipulate the list boxes from the command button

Private Sub cmdActivity_Click()
Dim strWhere As String

DoCmd.Hourglass True

Call ResetScreen(4)

With Me.lstActivity
strWhere = FindWhere(4)
If .RowSource = "" Then
.RowSource = "SELECT DISTINCT tblBudgetVSActualLbrPO.activity "
& _
"FROM tblBudgetVSActualLbrPO RIGHT JOIN tblActivity ON " & _
"tblBudgetVSActualLbrPO.Activity = tblActivity.actvActivity " & _
"WHERE Len(Trim(Nz(tblBudgetVSActualLbrPO.activity,''))) > 0" &
strWhere & ";"
End If
If .ListCount = 0 Then
Me.lblActivity.Caption = "No Matches"
Me.lblActivity.ForeColor = 255
.Height = 0
Me.cmdBillNetwork.SetFocus
Else
.Height = 3015
.SetFocus
End If
End With
DoCmd.Hourglass False
End Sub


Private Function FindWhere(lngSelector As Long) As String
Dim strWhere As String

If lngSelector = 1 Then
strWhere = BuildWhereCondition("lstPool")
If Len(strWhere) > 0 Then
strWhere = " AND LaborType2 " & strWhere
End If
End If

If lngSelector <= 2 Then
If (lngSelector < 2 And strWhere = "") Or lngSelector > 1 Then
strWhere = BuildWhereCondition("lstBillNetwork")
If Len(strWhere) > 0 Then
strWhere = " AND actvContractActivity " & strWhere
End If
End If
End If

If lngSelector <= 3 Then
If (lngSelector < 3 And strWhere = "") Or lngSelector > 2 Then
strWhere = BuildWhereCondition("lstActivity")
If Len(strWhere) > 0 Then
strWhere = " AND Activity " & strWhere
End If
End If
End If

If lngSelector <= 4 Then
If (lngSelector < 4 And strWhere = "") Or lngSelector > 3 Then
strWhere = BuildWhereCondition("lstMActivity")
If Len(strWhere) > 0 Then
strWhere = " AND actvParentActivity " & strWhere
End If
End If
End If

If lngSelector <= 5 Then
If (lngSelector < 5 And strWhere = "") Or lngSelector > 4 Then
strWhere = BuildWhereCondition("lstBillProdOffering")
If Len(strWhere) > 0 Then
strWhere = " AND BillableProductOffering " & _
strWhere
End If
End If
End If

FindWhere = strWhere

End Function