Filter on Report

[Willem2904]

Hi,

I have a report which I want to sort based on the values in textboxes and comboboxes.
The code below does the job, but only for 1 textbox/combobox at a time...
I want to apply the filter based on several boxes simultaneously...
Could you guys help me?

Private Function ApplyFilter() As String

Dim ctl As Control

Dim sName As String
Dim sFieldName As String
Dim sValue As String
Dim sFilter As String
Dim include As Boolean
On Error Resume Next
For Each ctl In Me.Controls
With ctl
Select Case .ControlType
Case acComboBox
If IsNull(.Value) Or .Value = "" Then
Else
sName = .Name

If sName = "cmbLocation" Then
sValue = Me.cmbLocation.Value
sFieldName = "[Location]"
include = True
ElseIf sName = "cmbGoods" Then
include = True
sFieldName = "[GoodsNo]"
sValue = Me.cmbGoods.Value
ElseIf sName = "cmbDepartament" Then
include = True
sFieldName = "[Department]"
sValue = Me.cmbDepartament.Value
End If

If include Then
If sFilter = "" Then
sFilter = sFieldName & " = " & """" & sValue & """"
Else
sFilter = sFilter & " OR " & sFieldName & " = " & """" & sValue & """"
End If
End If
include = False
End If

End Select
End With


With ctl
Select Case .ControlType
Case acTextBox
.SetFocus
If IsNull(.Value) Or .Value = "" Then

Else
sName = .Name
sFieldName = "[" & Right(.Name, Len(.Name) - 2) & "]"

If sName = "tbPartNo" Then
sValue = Me.tbPartNo.Value
include = True
ElseIf sName = "tbPartDesc" Then
include = True
sValue = Me.tbPartDesc.Value
ElseIf sName = "tbSerialNo" Then
include = True
sValue = Me.tbSerialNo.Value
ElseIf sName = "tbNoPartDesc" Then
include = True
sValue = Me.tbNoPartDesc.Value
sFieldName = "[No-PartDesc]"
ElseIf sName = "tbTransactionNo" Then
include = True
sValue = Me.tbTransactionNo.Value
End If

'And [No-PartDesc] Like "*" & value & "*"

If include Then
If sFilter = "" Then
'sFilter = sFieldName & Chr(34) & sValue & Chr(34)
'Debug.Print sFieldName & " Like " & """" & "*" & sValue & "*" & """"
sFilter = sFieldName & " Like " & """" & "*" & sValue & "*" & """"
Else
'sFilter = sFilter & " Like " & sFieldName & Chr(34) & sValue & Chr(34)
sFilter = sFilter & " OR " & sFieldName & " Like " & """" & "*" & sValue & "*" & """"
End If
End If
include = False
End If
End Select
End With

Next ctl
ApplyFilter = sFilter
End Function

 

[arnelgp]

why not use a form and leave the purpose of report to just reporting.

 

[Willem2904]

Because I want to see a report, not a form.