I think I am getting closer with this list box but received the following error:
Run time error '3075'
Invalid use of '.','!', or '()'. in query expression '[lstMachID] IN ("E148","E161","E995")([WorkTypeID] = "P2R") AND ([ActivityID]="P") AND ({ProdDate] >=#06/15/2017#) AND ([ProdDate] < #06/16/2017#'.
lstMachID is my listbox with E148, E161, and E995 being the machine names I selected from the listbox. It looks like I am missing the "AND" between the machine selections and the WorkTypeID field.
Could you tell me what is wrong with my listbox code below?
Private Sub cmdFilter_Click()
'Purpose:Build up the criteria string from the non-blank search boxes, and apply to the form's Filter.
'Notes:1. We tack " AND " on the end of each condition so you can easily add more search boxes; _
we remove the trailing " AND " at the end.
'2. The date range works like this: _
Both dates= only dates between (both inclusive. _
Start date only = all dates from this one onwards; _
End date only= all dates up to (and including this one).
Dim strWhere As String'The criteria string.
Dim lngLen As Long'Length of the criteria string to append to.
Const conJetDate = "\#mm\/dd\/yyyy\#"'The format expected for dates in a JET query string.
Dim varItem As Variant
Dim strCriteria As String
Dim strDelim As String'Delimiter for this field type
Dim strDescrip As String
Dim ctl As Control
strDelim = """"
'***********************************************************************
'Look at each search box, and build up the criteria string from the non-blank ones.
'***********************************************************************
'List box example by VF
'Loop through the ItemsSelected in the list box.
With Me.lstMachID
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
'Build up the filter from the bound column
strWhere = strWhere & strDelim & .ItemData(varItem) & strDelim & ","
'Build up the description from the text in the visible column. See note 2.
strDescrip = strDescrip & """" & .Column(1, 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 = "[lstMachID] IN (" & Left$(strWhere, lngLen) & ")"
lngLen = Len(strDescrip) - 2
If lngLen > 0 Then
strDescrip = "MachineType: " & Left$(strDescrip, lngLen)
End If
End If
