Combine 3 List box Choices

[Dan @BCBS]

From my point of view "I'm so close"..
This code below is from a command button on a form.
There are 3 list boxes that the user chooses from.

In this code below each of these choices work fine individually.

Could you please help me combine them for the results.
Example: If stReviewerList <> ")" And stLocationList <> ")" And
stProductList<> ")" Then stLinkCriteria =?????? Else If one of them blank
data based on other two, Else if 2 blank data based on just the one not
blank...

Can you please HELP.

Dim stDocName As String
Dim stLinkCriteria As String
Dim X As Integer
Dim Y As Integer
Dim Z As Integer
Dim stReviewer As Variant
Dim stLocation As Variant
Dim stProduct As Variant

stReviewerList = ""
stLocationList = ""
stProductList = ""

If IsNull(txtStart) Or IsNull(txtEnd) Then
MsgBox "Please enter start and end dates"
Exit Sub

Else

X = 0
For Each stReviewer In ListReviewer.ItemsSelected
If Not (IsNull(Me.ListReviewer)) Then
stLinkCriteria = "[Reviewer]=" & "'" & Me.ListReviewer & "'"
End If

X = X + 1
Next stReviewer

stReviewerList = stReviewerList & ")"

Y = 0
For Each stLocation In ListArea.ItemsSelected
If Not (IsNull(Me.ListArea)) Then
stLinkCriteria = "[GBULocation]=" & "'" & Me.ListArea & "'"
End If

Y = Y + 1
Next stLocation

stLocationList = stLocationList & ")"

Z = 0
For Each stProduct In ListProduct.ItemsSelected
If Not (IsNull(Me.ListProduct)) Then
stLinkCriteria = "[InsuranceType]=" & "'" & Me.ListProduct & "'"
End If

Z = Z + 1
Next stProduct

stProductList = stProductList & ")"
End If

stDocName = "Report1"
DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria

 

[TonyT]

The code For Each.......ItemsSelected is used for multiple Selections within
the combobox, yet you are using stLinkCriteria = "[Reviewer]=........ which
assumes no Multi Select option.

You can simplify to;

If Not IsNull(Me.ListReviewer Then
stLinkCriteria = "[Reviewer] = " & "'" & Me.ListReviewer & "'"
End If

completely losing the for each....next stuff and the next combo as,

If Not IsNull(me.ListArea) Then
If stLinkCriteria <> "" Then
stLinkCriteria = stLinkCriteria & " AND " & "[GBULocation] = " & "'" &
me.ListArea & "'"
Else: stLinkCriteria = "[GBULocation] = " & "'" & me.ListArea & "'"
End If
End If

Make sure you leave sufficient spaces when concatenating the lines!

Then exactly the same for the 3rd combobox as the second
leaving;

stDocName = "Report1"
DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria

as is, because the AND's will have made an acceptable string for the Report
to open on.

do the stLocationList and other st...List variables have a purpose
elsewhere? as they serve no purpose in this code, likewise the XY and Z
variables.

Hope this helps,

TonyT..

From my point of view "I'm so close"..
This code below is from a command button on a form.
There are 3 list boxes that the user chooses from.

In this code below each of these choices work fine individually.

Could you please help me combine them for the results.
Example: If stReviewerList <> ")" And stLocationList <> ")" And
stProductList<> ")" Then stLinkCriteria =?????? Else If one of them blank
data based on other two, Else if 2 blank data based on just the one not
blank...

Can you please HELP.

Dim stDocName As String
Dim stLinkCriteria As String
Dim X As Integer
Dim Y As Integer
Dim Z As Integer
Dim stReviewer As Variant
Dim stLocation As Variant
Dim stProduct As Variant

stReviewerList = ""
stLocationList = ""
stProductList = ""

If IsNull(txtStart) Or IsNull(txtEnd) Then
MsgBox "Please enter start and end dates"
Exit Sub

Else

X = 0
For Each stReviewer In ListReviewer.ItemsSelected
If Not (IsNull(Me.ListReviewer)) Then
stLinkCriteria = "[Reviewer]=" & "'" & Me.ListReviewer & "'"
End If

X = X + 1
Next stReviewer

stReviewerList = stReviewerList & ")"

Y = 0
For Each stLocation In ListArea.ItemsSelected
If Not (IsNull(Me.ListArea)) Then
stLinkCriteria = "[GBULocation]=" & "'" & Me.ListArea & "'"
End If

Y = Y + 1
Next stLocation

stLocationList = stLocationList & ")"

Z = 0
For Each stProduct In ListProduct.ItemsSelected
If Not (IsNull(Me.ListProduct)) Then
stLinkCriteria = "[InsuranceType]=" & "'" & Me.ListProduct & "'"
End If

Z = Z + 1
Next stProduct

stProductList = stProductList & ")"
End If

stDocName = "Report1"
DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria