Select blank line in multiselect list box and retrieve related data

[BarbQb]

Hi All,

I currently have a form with a multiselect list box that shows Vehicle IDs (Column 0) and Vehicle Statuses (Column 1) by customer. The VehicleID column is the bound column. Occasionally the VehicleID is blank for the customer, but there is associated data with it that can be retrieved (the Vehicle Status is not blank). However, when I select the blank line in the list box I can not populate the query/report related to that selection.

My question is: How can I retrieve the related data in the query/report when I select a blank line? Can I replace the blank in the comma delimited list with Is Null?

The following code makes the selected Vehicle IDs into a comma delimited list for my query criteria:

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 strWhere As String      'String to use as WhereCondition
    Dim strDescrip As String    'Description of WhereCondition
    Dim lngLen As Long          'Length of string
    Dim strDelim As String      'Delimiter for this field type.
    Dim strDoc As String        'Name of report to open.
    
    strDelim = """"            'Delimiter appropriate to field type. See note 1."
    strDoc = "rptAllCust_Customer"

    'Loop through the ItemsSelected in the list box.
    With Me.VehID
        For Each varItem In .ItemsSelected
            If Not IsNull(varItem) Then
                'Build up the filter from the bound column (hidden).
                strWhere = strWhere & strDelim & .ItemData(varItem) & strDelim & ","
                'Build up the description from the text in the visible column. See note 2.
                strDescrip = strDescrip & """" & .Column(0, varItem) & """, "
            End If
        Next
    End With
    
    'Remove trailing comma. Add field name, IN operator, and brackets.
    lngLen = Len(strWhere) - 1
    If lngLen > 0 Then
        strWhere = "[VEHICLE_ID] IN (" & Left$(strWhere, 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(strDoc).IsLoaded Then
        DoCmd.Close acReport, strDoc
    End If
    
    'Omit the last argument for Access 2000 and earlier. See note 4.
    DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere, OpenArgs:=strDescrip

Exit_Handler:
    Exit Sub

Err_Handler:
    If Err.Number <> 2501 Then  'Ignore "Report cancelled" error.
        MsgBox "Error " & Err.Number & " - " & Err.Description, , "PrevRelease_Click"
    End If
    Resume Exit_Handler

Any help would be great. Thanks.

[ADezii]

The addition of Code Line #5 to the strWhere Clause should do the trick:

'Remove trailing comma. Add field name, IN operator, and brackets.
    lngLen = Len(strWhere) - 1
    If lngLen > 0 Then
      strWhere = "[VEHICLE_ID] IN (" & Left$(strWhere, lngLen) & ")"
      strWhere = strWhere & " OR ([VEHICLE_ID] Is Null AND [Vehicle Status] Is Not Null);"
      
      lngLen = Len(strDescrip) - 2
      If lngLen > 0 Then
        strDescrip = "Categories: " & Left$(strDescrip, lngLen)
      End If
    End If

[BarbQb]

Thanks ADezii. You are a lifesaver. I just tested it out and it works perfectly.

[ADezii]

You are quite welcome, glad it worked out for you.