Multiple Multi-Select Boxes to Filter Report

[jon6035]

I have a form that utilizes a multi-select list box to filter the report on load.

Currently I have two, but will add a third, once I figure this problem out.

When I select a value from both lstPlatoon and lstTrainingType, the report works like a charm. However, the most common response will be to select the "cmdTEEPMultiSelect" without applying a filter to any of the boxes.

When I do this, I get a syntax error that reads ' AND ' Missing operator.

I have tried to clear the error, but am stumped. Without the multiple strings in the stLinkCriteria, it handles the no selection quite well.

Any help would be greatly appreciated.


Private Sub cmdTEEPMultiSelect_Click()
On Error GoTo Err_Handler
'Purpose: Open the report filtered to the items selected in the list box.
'Author: Allen J Browne, 2004. http://allenbrowne.com
Dim varItem As Variant 'Selected items
Dim strWherePLT As String 'String to use as WhereCondition
Dim strWhereTrainingType As String
Dim strDescrip As String 'Description of WhereCondition
Dim lngLen As Long 'Length of string
Dim strDelim As String 'Delimiter for this field type.
Dim stDocName As String 'Name of report to open.
Dim stQueryName As String
Dim stLinkCriteria As String

'http://allenbrowne.com/ser-50.html

'Use design view of query to help determine structure of SQL statement.
DoCmd.SetWarnings False

DoCmd.RunSQL ("DELETE temptblCalculatedStartEnd.* FROM temptblCalculatedStartEnd")
DoCmd.RunSQL ("INSERT INTO temptblCalculatedStartEnd SELECT selCalculatedStartEnd.* FROM selCalculatedStartEnd")
DoCmd.SetWarnings True



Select Case Me.frmTEEPLength.Value

Case 33
stDocName = "rptTEEP33Days"

Case 90
stDocName = "rptTEEP90Days"

End Select
'Loop through the ItemsSelected in the list box.

With Me.lstPlatoon
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
'Build up the filter from the bound column (hidden).
strWherePLT = strWherePLT & strDelim & .ItemData(varItem) & strDelim & ","

End If
Next
End With

'Remove trailing comma. Add field name, IN operator, and brackets.
lngLen = Len(strWherePLT) - 1
If lngLen > 0 Then
strWherePLT = "[PlatoonLU] IN (" & Left$(strWherePLT, lngLen) & ")"
lngLen = Len(strDescrip) - 2
If lngLen > 0 Then
strDescrip = "Categories: " & Left$(strDescrip, lngLen)
End If
End If
With Me.lstTrainingTypes
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
'Build up the filter from the bound column (hidden).
strWhereTrainingType = strWhereTrainingType & strDelim & .ItemData(varItem) & strDelim & ","
End If
Next
End With

'Remove trailing comma. Add field name, IN operator, and brackets.
lngLen = Len(strWhereTrainingType) - 1
If lngLen > 0 Then
strWhereTrainingType = "[TrainingTypeTLU] IN (" & Left$(strWhereTrainingType, lngLen) & ")"
lngLen = Len(strDescrip) - 2
If lngLen > 0 Then
strDescrip = "Categories: " & Left$(strDescrip, lngLen)
End If
End If

'Report will not filter if open, so close it. For Access 97, see note 3.
If CurrentProject.AllReports(stDocName).IsLoaded Then
DoCmd.Close acReport, stDocName
End If
Debug.Print
'This is where I need help.
stLinkCriteria = strWherePLT & " AND " & strWhereTrainingType

If either strWherePLT or strWhereTrainingType is null I get a missing operator error.

'Omit the last argument for Access 2000 and earlier. See note 4.
DoCmd.OpenReport stDocName, acViewPreview, , stLinkCriteria
Exit_Handler:
Exit Sub
Err_Handler:
If Err.Number <> 2501 Then 'Ignore "Report cancelled" error.
MsgBox "Error " & Err.Number & " - " & Err.Description, , "cmdPreview_Click"
End If
Resume Exit_Handler
Err_cmdTEEPMultiSelect_Click:

End Sub

[June7]

Variables strWherePLT and strWhereTrainingType are declared as string type. String type cannot be set to Null. The error should be occurring on the lines where you set those variables. Have you step debugged? Why is there a null in list box?