Report Filter in Property Sheet - Filter By Multiple Fields (1 Viewer)

ChrisMore

Member
Local time
Today, 08:51
Joined
Jan 28, 2020
Messages
174
Hi,

I am using the Filter property in the Report Property Sheet and it is working great when filtering one field. However, I would like to filter by multiple fields at one time.

This is the syntax I am currently using in the filter property: Date_Due_In =forms![Purchase_Orders_Filter_Pop_Up_Form]![text16]

I have a form called 'Purchase_Orders_Filter_Pop_Up_Form' which the user uses to enter the value they would like to filter the report by. I have created it so there is the possibility of the user entering the 'product code' as well as the 'date due in' to filter the report by both fields. Is it possible to filter multiple fields at one time using the filter property or so the user can choose which option they would like to filter by?

Thanks for you help!
Chris
 

theDBguy

I’m here to help
Staff member
Local time
Today, 01:51
Joined
Oct 29, 2018
Messages
21,358
Hi Chris. Yes, you can filter a form or report using more than one field. The syntax usually looks like this:

Field1 = Something AND Field2 = Something

That means the report will only show where both fields match. If you want to show either fields when they match, you would use the OR operator, instead of AND.
 

ChrisMore

Member
Local time
Today, 08:51
Joined
Jan 28, 2020
Messages
174
Hi Chris. Yes, you can filter a form or report using more than one field. The syntax usually looks like this:

Field1 = Something AND Field2 = Something

That means the report will only show where both fields match. If you want to show either fields when they match, you would use the OR operator, instead of AND.
Thanks for your reply theDBguy.

I am unable to get that working. Please can you provide an example of the the syntax using my current syntax as reference? FYI, the 'Product_Code' is typed into 'text11'.

Also, how would I get around the fact that the user may want to filter by two fields or just one field or might not want to filter at all? Is this pushing the capabilities of the report filter property on a single report and there is a better alternative?

Thanks,
Chris
 

theDBguy

I’m here to help
Staff member
Local time
Today, 01:51
Joined
Oct 29, 2018
Messages
21,358
Thanks for your reply theDBguy.

I am unable to get that working. Please can you provide an example of the the syntax using my current syntax as reference? FYI, the 'Product_Code' is typed into 'text11'.

Also, how would I get around the fact that the user may want to filter by two fields or just one field or might not want to filter at all? Is this pushing the capabilities of the report filter property on a single report and there is a better alternative?

Thanks,
Chris
Hi Chris. So, you have a form where the user would enter some criteria for the report. But, how exactly do they open that report?
 

ChrisMore

Member
Local time
Today, 08:51
Joined
Jan 28, 2020
Messages
174
Hi Chris. So, you have a form where the user would enter some criteria for the report. But, how exactly do they open that report?
The form has a command button which the user clicks to open the report.
This is the command button code:

Code:
Private Sub Command14_Click()
On Error GoTo MyError


    DoCmd.Close acReport, "Active_Purchase_Orders_Report", acSaveNo
    DoCmd.OpenReport "Active_Purchase_Orders_Report", acViewReport
    DoCmd.Close acForm, "Stocksheet_Dashboard_Form", acSaveNo

Leave:

    Exit Sub
    
MyError:

    MsgBox "Error " & Err.Number & ": " & Error$
    Resume Leave
End Sub

Thanks,
Chris
 

theDBguy

I’m here to help
Staff member
Local time
Today, 01:51
Joined
Oct 29, 2018
Messages
21,358
The form has a command button which the user clicks to open the report.
This is the command button code:

Code:
Private Sub Command14_Click()
On Error GoTo MyError


    DoCmd.Close acReport, "Active_Purchase_Orders_Report", acSaveNo
    DoCmd.OpenReport "Active_Purchase_Orders_Report", acViewReport
    DoCmd.Close acForm, "Stocksheet_Dashboard_Form", acSaveNo

Leave:

    Exit Sub
   
MyError:

    MsgBox "Error " & Err.Number & ": " & Error$
    Resume Leave
End Sub

Thanks,
Chris
Hi Chris. I thought so. Did you know the OpenReport method has a WhereCondition argument? It means you don't even have to mess with the Report's Filter property at all.

For instance, if you wanted to filter the report on one field, you might put this in the Filter property.

Code:
"FieldName=SomeValue"

Then, if you decide to filter a different field, you'll have to change the design of the report and use this one.

Code:
"OtherFieldName=SomeOtherValue"

Whereas, if you use the WhereCondition argument when you open the report, you can pass to it any filter criteria you want, and the report will open with that filter automatically applied. So, you don't have to change the design of the report at all.
 

ADIGA88

Member
Local time
Today, 11:51
Joined
Apr 5, 2020
Messages
94
Hi Chris
for me usually, I filter using two operators ( =, Like) it goes like this:
Code:
' Example #1
Private Sub cmdSearch_Click()

    Me.Filter = " strInvoiceNo & strVendorNo Like '*" & Me.txtSearch & "*'"
    Me.FilterOn = True
    
End Sub

' Example #2
Private Sub cmdSearch_Click()

    Me.Filter = " strInvoiceNo ='" & Me.txtbCriteria1 &  "' AND strVendorNo = '" & Me.txtCriteria2 & "'"
    Me.FilterOn = True
    
End Sub

if you have a problem with what Me. used for or the quotation marks just say and I can explain.
 

ChrisMore

Member
Local time
Today, 08:51
Joined
Jan 28, 2020
Messages
174
Hi Chris. I thought so. Did you know the OpenReport method has a WhereCondition argument? It means you don't even have to mess with the Report's Filter property at all.

For instance, if you wanted to filter the report on one field, you might put this in the Filter property.

Code:
"FieldName=SomeValue"

Then, if you decide to filter a different field, you'll have to change the design of the report and use this one.

Code:
"OtherFieldName=SomeOtherValue"

Whereas, if you use the WhereCondition argument when you open the report, you can pass to it any filter criteria you want, and the report will open with that filter automatically applied. So, you don't have to change the design of the report at all.
Hi theDBguy,

I tried the following:
Code:
 DoCmd.OpenReport "Active_Purchase_Orders_Report", acViewReport, , "Due_In_Date=#" & Me.Text16 & "#" And "Product_Code=" & Me.Text11

But I am getting a Type mismatch error so must be doing it wrong.

Will the Wherecondition work even when the user doesn't enter the criteria to filter, i.e enters into one or none of the text boxes? Or will the Wherecondition always expect two values to be entered?

Thanks,
Chris
 

ChrisMore

Member
Local time
Today, 08:51
Joined
Jan 28, 2020
Messages
174
Hi Chris
for me usually, I filter using two operators ( =, Like) it goes like this:
Code:
' Example #1
Private Sub cmdSearch_Click()

    Me.Filter = " strInvoiceNo & strVendorNo Like '*" & Me.txtSearch & "*'"
    Me.FilterOn = True
   
End Sub

' Example #2
Private Sub cmdSearch_Click()

    Me.Filter = " strInvoiceNo ='" & Me.txtbCriteria1 &  "' AND strVendorNo = '" & Me.txtCriteria2 & "'"
    Me.FilterOn = True
   
End Sub

if you have a problem with what Me. used for or the quotation marks just say and I can explain.
Hi Adiga88,

I tried the following but again I got a Type mismatch error, probably for the same reason as my previous reply

Code:
Me.Filter = " Due_In_Date =#" & Me.Text16 & "#" And "Product_Code = " & Me.Text11
    Me.FilterOn = True

Thanks,
Chris
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 16:51
Joined
May 7, 2009
Messages
19,170
Code:
Private Sub Command14_Click()
On Error GoTo MyError

    ' check if the report is open
    If SysCmd(acSysCmdGetObjectState, acReport, "Active_Purchase_Orders_Report") <> 0 Then
        'the report is open, close it
        DoCmd.Close acReport, "Active_Purchase_Orders_Report", acSaveNo
    End If
    DoCmd.OpenReport "Active_Purchase_Orders_Report", acViewReport, , _
        "Due_In_Date=#" & Format(Me.Text16, "mm\/dd\/yyyy") & "# And Product_Code='" & Me.Text11 & "'"
    DoCmd.Close acForm, "Stocksheet_Dashboard_Form", acSaveNo

Leave:

    Exit Sub
    
MyError:

    MsgBox "Error " & Err.Number & ": " & Error$
    Resume Leave
End Sub
 

ADIGA88

Member
Local time
Today, 11:51
Joined
Apr 5, 2020
Messages
94
Hi Adiga88,

I tried the following but again I got a Type mismatch error, probably for the same reason as my previous reply
yes it's, you should do to string the same thing you did to dates.

Code:
'numbers need nothing Example:
Me.Filter = " intNo = 15"
'With  Variables   
Me.Filter = " intNo = " &  Me.txtNumber
Code:
'String need single quotes Example:
Me.Filter = " strName = 'Joe'"
'With  Variables:
Me.Filter = " strName = '" &  Me.txtString & "'"
Code:
'dates needs ## Example:
Me.Filter = " datDate = #19-06-2020#"
'With  Variables
Me.Filter = " datDate = #" & Me.txtDate & "#"
 

ChrisMore

Member
Local time
Today, 08:51
Joined
Jan 28, 2020
Messages
174
Code:
Private Sub Command14_Click()
On Error GoTo MyError

    ' check if the report is open
    If SysCmd(acSysCmdGetObjectState, acReport, "Active_Purchase_Orders_Report") <> 0 Then
        'the report is open, close it
        DoCmd.Close acReport, "Active_Purchase_Orders_Report", acSaveNo
    End If
    DoCmd.OpenReport "Active_Purchase_Orders_Report", acViewReport, , _
        "Due_In_Date=#" & Format(Me.Text16, "mm\/dd\/yyyy") & "# And Product_Code='" & Me.Text11 & "'"
    DoCmd.Close acForm, "Stocksheet_Dashboard_Form", acSaveNo

Leave:

    Exit Sub
  
MyError:

    MsgBox "Error " & Err.Number & ": " & Error$
    Resume Leave
End Sub
Hi arnelgp,

Thank you for your input, I have got that working now. However, how can I change the code to allow the user to only select one criteria (such as product code, so all due in dates show for the specified product code) or none at all (so all records show in the report)? Currently an error appears saying there is a missing operator if I don't enter one of the criteria.

Thanks,
Chris
 
Last edited:

isladogs

MVP / VIP
Local time
Today, 08:51
Joined
Jan 14, 2017
Messages
18,186
For situations where the number of filters conditions may vary (or no filtering be applied) it is usually best to build your filter string in code.
For two examples showing how to do this with multiple criteria including text/number/date, see Multiple Group & Filter
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 16:51
Joined
May 7, 2009
Messages
19,170
add variables and check each variables if they are available:
Code:
Private Sub Command14_Click()
On Error GoTo MyError
    Dim strDueDateCriteria As String
    Dim strProductCriteria As String
    Dim strCriteria As String
    ' check if the report is open
    If SysCmd(acSysCmdGetObjectState, acReport, "Active_Purchase_Orders_Report") <> 0 Then
        'the report is open, close it
        DoCmd.Close acReport, "Active_Purchase_Orders_Report", acSaveNo
    End If
    If Not IsNull(Me!text16) Then
        strDueDateCriteria = "Due_In_Date=#" & Format(Me.text16, "mm\/dd\/yyyy") & "# And "
    End If
    If Not IsNull(Me!Text11) Then
        strProductCriteria = "Product_Code='" & Me.Text11 & "'"
    End If
    strCriteria = strDueDateCriteria & strProductCriteria
    If Len(strCriteria) > 0 Then
        If Right$(strCriteria, 4) = "And " Then
            strCriteria = Trim$(Left$(strCriteria, Len(strCriteria) - 4))
        End If
        DoCmd.OpenReport "Active_Purchase_Orders_Report", acViewReport, , _
            strCriteria
            
    Else
        DoCmd.OpenReport "Active_Purchase_Orders_Report", acViewReport
    
    End If

Leave:

    Exit Sub
 
MyError:

    MsgBox "Error " & Err.Number & ": " & Error$
    Resume Leave
End Sub

errHandler:

MsgBox Err.Number & " - " & Err.Description, vbOKOnly, "Error ..."

End Sub
 

Users who are viewing this thread

Top Bottom