Filtering Report Using Dialog Box (1 Viewer)

[catfan]

I have set up a form to filter my report with. I want to be able to filter my name field using a text box and typing in a comma separated string that would use the "contains" wildcard for each item in the string. Is it possible to filter by multiple "contains" using a text box? If so, please help me with the code. Here is what I have so far for code.

Private Sub cmdApplyFilter_Click()
Dim strName As String
Dim strProjectUseFunction As String
Dim strFilter As String
Dim strYearCompleted As String
' Check that the report is open
If SysCmd(acSysCmdGetObjectState, acReport, "Barb's report") <> acObjStateOpen Then
MsgBox "You must open the report first."
Exit Sub
End If
' Build Name criteria string
If IsNull(Me.txtName.Value) Then
strName = "Like '*'"
Else
strName = "Like '*" & Me.txtName.Value & "*'"

' Build Use / Function criteria string
If IsNull(Me.txtProjectUseFunction.Value) Then
strProjectUseFunction = "Like '*'"
Else
strProjectUseFunction = "Like '*" & Me.txtProjectUseFunction.Value & "*'"
End If
' Build Year Completed >
If IsNull(Me.txtYearCompleted.Value) Then
strYearCompleted = "Like '*'"
Else
strYearCompleted = "> '" & Me.txtYearCompleted.Value & "'"
End If
' Build filter string
strFilter = "[Name] " & strName & _
" AND [Project Use / Function] " & strProjectUseFunction & _
" AND [Year Completed] " & strYearCompleted
' Apply filter to report
With Reports![Barb's report]
.Filter = strFilter
.FilterOn = True
End With
End Sub

Private Sub cmdRemoveFilter_Click()
On Error Resume Next
Reports![Barb's report].FilterOn = False
End Sub

 

[jstutz]

Use a Function to Parse Name string

If I understand you correctly, probably the easiest way is to use a function to parse your name string. Below is an example fucntion including a sample input string to the function and what it returns:

FUNCTION INPUT:
aa,b,c,d

FUNCTION RETURNS:
[NAME] Like '*aa*' OR Like '*b*' OR Like '*c*' OR Like '*d*'


Private Function ParseName(strCriteria As String)
Dim arrStr() As String
Dim intArrCnt As Integer
Dim strOutput As String


'SPLIT THE STRING INTO AN ARRAY
'
arrStr = Split(strCriteria, ",")

'COUNT THE NUMBER OF ITEMS IN THE ARRAY
'
intArrCnt = UBound(arrStr)

'SET THE FIRST PART OF THE STRING
'
strOutput = "[NAME] Like '*" & arrStr(0) & "*'"

'LOOP THRU ARRAY TO BUILD STRING
'
For x = 1 To intArrCnt
strOutput = strOutput & " OR Like '*" & arrStr(x) & "*'"
Next x

ParseName = strOutput

End Function

 

[catfan]

Thanks for your help. I am not very good at coding. How do I tie this into my code for my text box (txtName) and my private sub for the apply filter button?

 

[jstutz]

I'm not entirely sure what you are asking. I THINK what you need to do is to:

1) Add the function example I've supplied to code module for your form.

2) Remove any code from your orignal that you were using to create the filter string. In it's place use the function to create the string by feeding it with the user input from your textbox and then trapping the function's result into a string vari. For example:

Dim strTextBoxValue as string
Dim strFilter as string

strTextBoxValue = me.MyTextBox
strFilter = ParseName(strTextBoxValue)

With Reports![Barb's report]
.Filter = strFilter
.FilterOn = True
End With​

Make sense?

j