A Multi-Select List box with Combo Boxes for Report Criteria

[ZaZa]

Hi everyone,

I need some help on this please if you can,

I am trying to find a simple, elegnat way of coding so that I can use the selection criteria in combo boxes and a multiselect list box to create the criteria for a report.

I have two combo boxes called
[SelectStatus} and [Priority] and a MULTI select list box called [SelectCSU].

If I have the following criteria selected:
[SelectStatus] = open
[SelectPriority] = A
[SelectCSU] = 25B, 26C, 27B

I want the final criteria to be:
strCriteria = [SelectStatus] = Open And [SelectPriority] = A and [SelectCSU] = 25B OR
[SelectStatus] = Open And [SelectPriority] = A and [SelectCSU] = 26C OR
[SelectStatus] = Open And [SelectPriority] = A and [SelectCSU] = 27B OR

Instead, the closest I have gotten is this:
strCriteria =[SelectStatus] = Open And [SelectPriority] = A And [SelectCSU]=25B OR [SelectCSU]=26C OR [SelectCSU]=27B

My original code was very large but thanks to prior help from this site I an on my way to slimming it down but I can't figure out this last bit. The code ( at an unknown state of evloution) is shown below,. I really really need some help here if anyone can spare the time.

----------------------------
Dim cntl As Control
Dim lngCounter As Long
Dim varItem As Variant

Dim strCriteria1 As String 'Variable to store StatusID
Dim strCriteria2 As String 'Variable to store Priority
Dim strCriteria3 As String 'Variable to store CSU


Set cntl = Me.SelectCSU
lngCounter = 0



If Not IsNull(Me![SelectStatus]) Then
strCriteria1 = "[StatusID] = " & Me![SelectStatus] & ""
End If

If Not IsNull(Me![SelectPriority]) Then
strCriteria2 = " [Priority] = " & Me![SelectPriority] & ""
End If

'strCriteria3 = strCriteria1 And strCriteria2

If cntl.ItemsSelected.Count <> 0 Then

For Each varItem In cntl.ItemsSelected
lngCounter = lngCounter + 1
If lngCounter = cntl.ItemsSelected.Count Then
strCriteria3 = strCriteria1 And strCriteria2 And &quot; [SystemID] = '&quot; & cntl.Column(0, varItem) & &quot;'&quot;
Else

strCriteria3 = strCriteria1 And strCriteria2 And &quot; [SystemID] = '&quot; & cntl.Column(0, varItem) & &quot;' or &quot;
End If
Next varItem
End If

'Open Report
DoCmd.OpenReport &quot;Rpt03&quot;, acViewPreview, , strCriteria3

'Clear the selections after the report is opened
Dim i As Integer

For i = 0 To (SelectCSU.ListCount - 1)
SelectCSU.Selected(i) = False
Next i

DoCmd.Close acForm, &quot;SelectionForm&quot;, acSaveYes
End Sub

---------------------

Please help , the intention is that once i get it working for 2 combo boxes, i will have to expand for 4 additional combo boxes.

Regards,
ZaZa

 

[ZaZa]

Hi,

For anyone who might be having this problem, the Marquis and MicahealRed helped me to sort it out in another post.

THe key was in creating several criteria and joining then at the end by using &quot;And&quot; and PARENTHESES to make a final criteria that will be used to open the report.

I've copied the code below for anyone interested.
---------------------------

Dim cntl As Control
Dim lngCounter As Long
Dim varItem As Variant

Dim strCriteria1 As String 'For Status and priority
Dim strCriteria2 As String 'Variable to store CSU
Dim strCriteria3 As String 'To join strCritera1 & 2

Set cntl = Me.SelectCSU
lngCounter = 0

If Not IsNull(Me![SelectStatus]) Then
strCriteria1 = &quot;[StatusID] = &quot; & Me![SelectStatus] & &quot;&quot;
Else
strCriteria1 = &quot;&quot;
End If

If Not IsNull(Me![SelectPriority]) Then
strCriteria1 = strCriteria1 & &quot; And [Priority] = &quot; & Me![SelectPriority] & &quot;&quot;
Else
strCriteria1 = strCriteria1
End If

If cntl.ItemsSelected.Count <> 0 Then

For Each varItem In cntl.ItemsSelected
lngCounter = lngCounter + 1
If lngCounter = cntl.ItemsSelected.Count Then
strCriteria2 = strCriteria2 & &quot; [SystemID] = '&quot; & cntl.Column(0, varItem) & &quot;' &quot;
Else
strCriteria2 = strCriteria2 & &quot; [SystemID] = '&quot; & cntl.Column(0, varItem) & &quot;' or &quot;
End If
Next varItem
End If

strCriteria3=strCriteria1 & &quot;And&quot; & &quot;(&quot; & strCriteria2 & &quot&quot;

'Open Report
DoCmd.OpenReport &quot;Rpt03&quot;, acViewPreview, , strCriteria3

------------------

Thanks again tek-tips for such wonderful support!

ZaZa