kengooch
Member
- Local time
- Yesterday, 16:57
- Joined
- Feb 29, 2012
- Messages
- 137
So I have a form that I need to be able to filter by a range of dates and by a selected value in a multi-select list box (actually using only one value at a time)
vStDate and vEndDate are unbound text boxes tied to the Date Picker in access. tEvType is a look up field that that gets its values from a table named tEvntTypes with two fields, tEvTypeID (AutoNumber) and tEvType (text field that describes the type)
The code to manage the Date filter is:
The multiselect list box can handle 5 boxes, but at this juncture I only use the one. Here is the code for it.
'= = = = = = = = = = = = = = = = = = = = = = = = = = = = =
'MULTI SELECT LIST BOXES - Referred to as MSL
'This code handles the Multi select list Boxes that allow interactive data selection
'Last Updated 08/14/2020
'This code adjusts the Form View according to the Specified MSLB Selections
'- - - - - - - - - - - - - - - - - - - - - - - - - - - - -
'Terminology and Notes
' msl = Multi Select List box
'- - - - - - - - - - - - - - - - - - - - - - - - - - - - -
'Form Fields being Filtered
' tField1 = tEvType 'This is another issue, I have tried to set the MSL's up so I can just change the forms field name but it errors out.
' tField2 = tYourField2
' tField3 = tYourField3
' tField4 = tYourField4
' tField5 = tYourFiled5
'(Search and Replace the following values for the Form you apply this to.'Search and Replace the following values for the Form you apply this to.)
'- - - - - - - - - - - - - - - - - - - - - - - - - - - - -
'This code handles the First List Box
Private Sub mslBox1_Click()
Me.Form.Filter = vSetFilters
Me.Form.FilterOn = True
Me.Form.Refresh
MsgBox "Pause" 'The above code filters perfectly
'The next line is where I am trying to combine the two filters into one that does both.
Me.Form.Filter = vSetFilters & vDateRng 'vDateRng is assigned in the Date filter portion.
Me.Form.FilterOn = True
Me.Form.Refresh
End Sub
'- - - - - - - - - - - - - - - - - - - - - - - - - - - - -
'This code handles the Second List Box
Private Sub mslBox2_Click()
Me.Form.Filter = vSetFilters
Me.Form.FilterOn = True
... Code Removed for Brevities sake
'- - - - - - - - - - - - - - - - - - - - - - - - - - - - -
'This code handles the Fourth List Box
Private Sub mslBox5_Click()
Me.Form.Filter = vSetFilters
Me.Form.FilterOn = True
Me.Form.Refresh
End Sub
'- - - - - - - - - - - - - - - - - - - - - - - - - - - - -
'Multi-Select List Boxes
'Last Modified 08/14/2020
'This code adjusts the Form View according to the Specified Selections
'- - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Public Function vSetFilters() As String
On Error GoTo vSetFilters_Err
' Assign Variables
Dim vIndvSel As String
Dim varItem As Variant
Dim vListBox1, vListBox2, vListBox3, vListBox4, VListBox5 As Control
'Define List Boxes
Set vListBox1 = Me.mslBox1
Set vListBox2 = Me.mslBox2
Set vListBox3 = Me.mslBox3
Set vListBox4 = Me.mslBox4
Set VListBox5 = Me.mslBox5
'- - - - - - - - - - - - - - - - - - - - - - - - - - - - -
'1st Multi Select List Box
'- - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Select Case vListBox1.ItemsSelected.Count
Case Is = 1
If Len(vIndvSel) >= 1 Then vIndvSel = vIndvSel & " AND "
vIndvSel = vIndvSel & "ABS([tEvntType])='"
For Each varItm In vListBox1.ItemsSelected
vIndvSel = vIndvSel & vListBox1.ItemData(varItm) & "'"
Next varItm
Case Is > 1
If Len(vIndvSel) >= 1 Then vIndvSel = vIndvSel & " AND "
vIndvSel = vIndvSel & "ABS([tEvntType]) IN ("
i = 0
For Each varItm In vListBox1.ItemsSelected
If i > 0 Then
vIndvSel = vIndvSel & ", "
End If
i = i + 1
vIndvSel = vIndvSel & Chr(34) & vListBox1.ItemData(varItm) & Chr(34)
Next varItm
vIndvSel = vIndvSel & ") "
End Select
'Assign String to Isolate Records
vSetFilters = vIndvSel
'- - - - - - - - - - - - - - - - - - - - - - - - - - - - -
'2nd Multi Select List Box
'- - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Select Case vListBox2.ItemsSelected.Count
Case Is = 1
...... code for 2 - 4 Removed for brevities sake.
'- - - - - - - - - - - - - - - - - - - - - - - - - - - - -
'5th Multi Select List Box
'- - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Select Case VListBox5.ItemsSelected.Count
Case Is = 1
If Len(vIndvSel) >= 1 Then vIndvSel = vIndvSel & " AND "
vIndvSel = vIndvSel & "[tField5]='"
For Each varItm In VListBox5.ItemsSelected
vIndvSel = vIndvSel & VListBox5.ItemData(varItm) & "'"
Next varItm
Case Is > 1
If Len(vIndvSel) >= 1 Then vIndvSel = vIndvSel & " AND "
vIndvSel = vIndvSel & "[tField5] IN ("
i = 0
For Each varItm In VListBox5.ItemsSelected
If i > 0 Then
vIndvSel = vIndvSel & ", "
End If
i = i + 1
vIndvSel = vIndvSel & Chr(34) & VListBox5.ItemData(varItm) & Chr(34)
Next varItm
vIndvSel = vIndvSel & ") "
End Select
'Assign String to Isolate Records
'This code handles the Fifth List Box
vSetFilters = vIndvSel
'- - - - - - - - - - - - - - - - - - - - - - - - - - - - -
vSetFilters_Exit:
Exit Function
vSetFilters_Err:
Select Case Err
Case Is = 2501
Resume Next
Case Else
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume vSetFilters_Exit
End Select
End Function
= = = = = = = = = = = = End of Code
So I use vIndvSel to capture each click of the MSL box. Then assign those to vSetFilters. Once all boxes are checked I use the concatenated filters in vSetFilters to filter the form.
So... I can either filter at the Me.Form.Filter = vSetFilters and try to figure out how to add on the Date filter
Me.Form.Filter = vSetFilters & vDateRng '(This doesn't work)
This is where I would prefer to set the filter...
- OR -
For the Date Filter, the following Code works.
Me.Filter = "[tEvntDate] BETWEEN #" & Me.vStDate & "# AND #" & Me.vEndDate & "# AND [tEvType] = 'Reaction'"
(Notice I hard coded one of the vEvType which is the description of the vEvTypeID and it filters correctly)
So... Does anybody know what the syntax would be to put append the tEvType or tEvTypeID together with the date filter vSetFilters. In other words, I can't figure out the Syntax to replace the hard coded 'Reaction'" with the MSL vSetFilters
vStDate and vEndDate are unbound text boxes tied to the Date Picker in access. tEvType is a look up field that that gets its values from a table named tEvntTypes with two fields, tEvTypeID (AutoNumber) and tEvType (text field that describes the type)
The code to manage the Date filter is:
The multiselect list box can handle 5 boxes, but at this juncture I only use the one. Here is the code for it.
'= = = = = = = = = = = = = = = = = = = = = = = = = = = = =
'MULTI SELECT LIST BOXES - Referred to as MSL
'This code handles the Multi select list Boxes that allow interactive data selection
'Last Updated 08/14/2020
'This code adjusts the Form View according to the Specified MSLB Selections
'- - - - - - - - - - - - - - - - - - - - - - - - - - - - -
'Terminology and Notes
' msl = Multi Select List box
'- - - - - - - - - - - - - - - - - - - - - - - - - - - - -
'Form Fields being Filtered
' tField1 = tEvType 'This is another issue, I have tried to set the MSL's up so I can just change the forms field name but it errors out.
' tField2 = tYourField2
' tField3 = tYourField3
' tField4 = tYourField4
' tField5 = tYourFiled5
'(Search and Replace the following values for the Form you apply this to.'Search and Replace the following values for the Form you apply this to.)
'- - - - - - - - - - - - - - - - - - - - - - - - - - - - -
'This code handles the First List Box
Private Sub mslBox1_Click()
Me.Form.Filter = vSetFilters
Me.Form.FilterOn = True
Me.Form.Refresh
MsgBox "Pause" 'The above code filters perfectly
'The next line is where I am trying to combine the two filters into one that does both.
Me.Form.Filter = vSetFilters & vDateRng 'vDateRng is assigned in the Date filter portion.
Me.Form.FilterOn = True
Me.Form.Refresh
End Sub
'- - - - - - - - - - - - - - - - - - - - - - - - - - - - -
'This code handles the Second List Box
Private Sub mslBox2_Click()
Me.Form.Filter = vSetFilters
Me.Form.FilterOn = True
... Code Removed for Brevities sake
'- - - - - - - - - - - - - - - - - - - - - - - - - - - - -
'This code handles the Fourth List Box
Private Sub mslBox5_Click()
Me.Form.Filter = vSetFilters
Me.Form.FilterOn = True
Me.Form.Refresh
End Sub
'- - - - - - - - - - - - - - - - - - - - - - - - - - - - -
'Multi-Select List Boxes
'Last Modified 08/14/2020
'This code adjusts the Form View according to the Specified Selections
'- - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Public Function vSetFilters() As String
On Error GoTo vSetFilters_Err
' Assign Variables
Dim vIndvSel As String
Dim varItem As Variant
Dim vListBox1, vListBox2, vListBox3, vListBox4, VListBox5 As Control
'Define List Boxes
Set vListBox1 = Me.mslBox1
Set vListBox2 = Me.mslBox2
Set vListBox3 = Me.mslBox3
Set vListBox4 = Me.mslBox4
Set VListBox5 = Me.mslBox5
'- - - - - - - - - - - - - - - - - - - - - - - - - - - - -
'1st Multi Select List Box
'- - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Select Case vListBox1.ItemsSelected.Count
Case Is = 1
If Len(vIndvSel) >= 1 Then vIndvSel = vIndvSel & " AND "
vIndvSel = vIndvSel & "ABS([tEvntType])='"
For Each varItm In vListBox1.ItemsSelected
vIndvSel = vIndvSel & vListBox1.ItemData(varItm) & "'"
Next varItm
Case Is > 1
If Len(vIndvSel) >= 1 Then vIndvSel = vIndvSel & " AND "
vIndvSel = vIndvSel & "ABS([tEvntType]) IN ("
i = 0
For Each varItm In vListBox1.ItemsSelected
If i > 0 Then
vIndvSel = vIndvSel & ", "
End If
i = i + 1
vIndvSel = vIndvSel & Chr(34) & vListBox1.ItemData(varItm) & Chr(34)
Next varItm
vIndvSel = vIndvSel & ") "
End Select
'Assign String to Isolate Records
vSetFilters = vIndvSel
'- - - - - - - - - - - - - - - - - - - - - - - - - - - - -
'2nd Multi Select List Box
'- - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Select Case vListBox2.ItemsSelected.Count
Case Is = 1
...... code for 2 - 4 Removed for brevities sake.
'- - - - - - - - - - - - - - - - - - - - - - - - - - - - -
'5th Multi Select List Box
'- - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Select Case VListBox5.ItemsSelected.Count
Case Is = 1
If Len(vIndvSel) >= 1 Then vIndvSel = vIndvSel & " AND "
vIndvSel = vIndvSel & "[tField5]='"
For Each varItm In VListBox5.ItemsSelected
vIndvSel = vIndvSel & VListBox5.ItemData(varItm) & "'"
Next varItm
Case Is > 1
If Len(vIndvSel) >= 1 Then vIndvSel = vIndvSel & " AND "
vIndvSel = vIndvSel & "[tField5] IN ("
i = 0
For Each varItm In VListBox5.ItemsSelected
If i > 0 Then
vIndvSel = vIndvSel & ", "
End If
i = i + 1
vIndvSel = vIndvSel & Chr(34) & VListBox5.ItemData(varItm) & Chr(34)
Next varItm
vIndvSel = vIndvSel & ") "
End Select
'Assign String to Isolate Records
'This code handles the Fifth List Box
vSetFilters = vIndvSel
'- - - - - - - - - - - - - - - - - - - - - - - - - - - - -
vSetFilters_Exit:
Exit Function
vSetFilters_Err:
Select Case Err
Case Is = 2501
Resume Next
Case Else
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume vSetFilters_Exit
End Select
End Function
= = = = = = = = = = = = End of Code
So I use vIndvSel to capture each click of the MSL box. Then assign those to vSetFilters. Once all boxes are checked I use the concatenated filters in vSetFilters to filter the form.
So... I can either filter at the Me.Form.Filter = vSetFilters and try to figure out how to add on the Date filter
Me.Form.Filter = vSetFilters & vDateRng '(This doesn't work)
This is where I would prefer to set the filter...
- OR -
For the Date Filter, the following Code works.
Me.Filter = "[tEvntDate] BETWEEN #" & Me.vStDate & "# AND #" & Me.vEndDate & "# AND [tEvType] = 'Reaction'"
(Notice I hard coded one of the vEvType which is the description of the vEvTypeID and it filters correctly)
So... Does anybody know what the syntax would be to put append the tEvType or tEvTypeID together with the date filter vSetFilters. In other words, I can't figure out the Syntax to replace the hard coded 'Reaction'" with the MSL vSetFilters