Form filter for a Report (1 Viewer)

GrahamUK33 · Mar 6, 2018

I am after some help creating a Form filter for a Report,

I have Form frmReportFilter that opens rptTimmsReport in the background. On frmReportFilter is a Combo Box (cboProject) that filters the records on rptTimmsReport once a project has been selected and a Button (cmdApplyFilter) has been selected.

What I am looking in addition is for the report to be filtered by either/or as well as other fields.

Date fields: txtStartDate, txtEndDate
Checkboxes: chkbox1 chkbox2 chkbox3

The VBA I currently have is:

Code:

Private Sub cmdApplyFilter_Click()
    Dim strProject As String
    Dim strFilter As String
    Dim strParliament As String
    
    If IsNull(Me.cboProject.Value) Then
        strProject = "Like '*'"
    Else
        strProject = "='" & Me.cboProject.Value & "'"
    End If

    strFilter = "[Project] " & strProject & ""

    With Reports![rptTimmsImpact]
        .Filter = strFilter
        .FilterOn = True
    End With
End Sub

Private Sub cmdRemoveFilter_Click()
    On Error Resume Next
        
    'Clear the following fields
    Me.cboProject = Null
    Me.txtStartDate = Null
    Me.txtEndDate = Null
    Me.chkCharity.Value = False
    Me.chkEducation.Value = False
    Me.chkMagazine.Value = False
    Me.chkNewspaper.Value = False
    Me.chkNHS.Value = False
    Me.chkParliament.Value = False
    Me.chkBroadcast.Value = False

    Reports![rptTimmsImpact].FilterOn = False
End Sub


Private Sub Form_Load()
    'Clear the following fields
    Me.cboProject = Null
    Me.txtStartDate = Null
    Me.txtEndDate = Null
    Me.chkCharity.Value = False
    Me.chkEducation.Value = False
    Me.chkMagazine.Value = False
    Me.chkNewspaper.Value = False
    Me.chkNHS.Value = False
    Me.chkParliament.Value = False
    Me.chkBroadcast.Value = False
    
    DoCmd.OpenReport "rptTimmsImpact", acViewReport 'acViewPreview
End Sub

Ranman256 · Mar 6, 2018

docmd.OpenReport "rMyReport",acViewPreview ,,sFilter

Mark_ · Mar 6, 2018

Code:

    If IsNull(Me.cboProject.Value) Then
        strProject = "Like '*'"
    Else
        strProject = "='" & Me.cboProject.Value & "'"
    End If

    strFilter = "[Project] " & strProject & ""

    With Reports![rptTimmsImpact]
        .Filter = strFilter
        .FilterOn = True
    End With

The logic to this is rather confusing. Like '*' should be the same as not having a filter at all. For your filter string you would be looking at ONLY having something in it if you wish to restrict the return set. As such the block above could be just

Code:

If not IsNull(Me.cboProject.Value) Then
    strProject = "[Project] ='" & Me.cboProject.Value & "'"
End If

With Reports![rptTimmsImpact]
   .Filter = strFilter
   .FilterOn = True
End With

If you wished to make this a bit more modular (and avoid redundant code) you could also have a function to append to your filter string

Code:

Public Function AppendFilter(pvAdd As Variant, pvFilter As Variant) As String
   If IsNull(pvAdd) Then ' If nothing to add
      If IsNull(pvFilter) Then 'If no filter passed
         AppendFilter = "" 'Return nothing
      Else 'if there WAS a filter
         AppendFilter = Trim(pvFilter) 'Just return that.
      End If
   Else 'If we DO have something to add,
      If IsNull(pvFilter) Then 'If no previous filter
         AppendFilter = Trim(pvAdd) ' return what was added
      Else 'otherwise
         AppendFilter = Trim(pvFilter) & [COLOR="Blue"][B]" OR "[/B][/COLOR] & Trim(pvAdd)
      End If
   End If
End Function

Change around the " OR " as needed for your particular needs.

This would change your code to be

Code:

If not IsNull(Me.cboProject.Value) Then
    strProject = AppendFilter("[Project] ='" & Me.cboProject.Value & "'",StrProject)
End If

Makes it rather tidy for each of the optional pieces.

GrahamUK33 · Mar 7, 2018

Thanks for the detailed reply, its much appreciated.

As well as filtering the report (rptTimmsImpact) on the combo box (cboProject), I am looking at filtering using a Start and End date, and 7 checkboxes.

txtStartDate
txtEndDate
chkCharity.Value
chkEducation.Value
chkMagazine.Value
chkNewspaper.Value
chkNHS.Value
chkParliament.Value
chkBroadcast

The VBA for just one checkbox below works on its own, but does not work as soon as I add it to the existing code. I have also tried to add code for the other checkboxes using the same code, but that does not work either.

Code:

    If Me.chkCharity = -1 Then
        strFilter = "([Charity] = True)"
    ElseIf Me.chkCharity = 0 Then
        strFilter = "([Charity] = False)"
    End If

    With Reports![rptTimmsImpact]
        .Filter = strFilter
        .FilterOn = True
    End With

I have not got any VBA to filter using a Start Date and End Date which uses a field called ‘Published’.

I have been adding and removing code for the last couple of days and not got anywhere with it, I would really appreciate someone to add some code that would work.

Mark_ · Mar 7, 2018

The trick is your

Code:

    With Reports![rptTimmsImpact]
        .Filter = strFilter
        .FilterOn = True
    End With

You want to fill in the ENTIRE filter before applying it. If you fill it in piece by piece you will only get the last one. As such, you code should be one block to create strFilter THEN go ahead and apply your filter.

So you will wind up with a block of code that looks something like

Code:

'Build up the filter string first.
StrFilter = ''
If not IsNull(Me.cboProject.Value) Then strProject = AppendFilter("[Project] ='" & Me.cboProject.Value & "'",StrProject)

IF nz(Me.StartDate,0) <>  0 then ' IF we have a start date, use nz so it is easy to check.
   if nz(Me.EndDate,0) <> 0 then  'and If we have an end date also,
      strFilter = AppendFilter( "([Publisher]  >= " & FORMAT( Me.StartDate, "\#mm\/dd\/yyyy\#" & " AND [Publisher] <= " & FORMAT(Me.EndDate, "\#mm\/dd\/yyyy\#")
   End If
End If

MsgBox "Filter to be " & strFilter   'Done so we know what will be sent, for debug purpses.

if StrFilter <> "" ' If there is a filter
   With Reports![rptTimmsImpact]
           .Filter = strFilter
           .FilterOn = True
    End With
End if

This way you have your filter build, THEN you apply your filter. The above should be close to what you will be using. As it may not match exactly I've put in the message box so you can review and see if there are any syntax issues.

GrahamUK33 · Mar 9, 2018

Thanks for looking at this.

What I am after is for the user to search on any of the fields and any combination of the fields.

When I use any of the fields to search on, I get a ‘Compile Error / syntax Error’ and then the code comes up highlighting:
StrFilter = ' '

I have uploaded a copy of the database to make it easier to debug.

Mark_ · Mar 9, 2018

Code:

Private Sub cmdApplyFilter_Click()
    Dim strProject As String
    Dim strFilter As String
    
    'Build up the filter string first.
    strFilter = ""
    If Not IsNull(Me.cboProject.Value) Then strProject = AppendFilter("[Project] ='" & Me.cboProject.Value & "'", strProject)

    If Nz(Me.StartDate, 0) <> 0 Then ' IF we have a start date, use nz so it is easy to check.
       If Nz(Me.EndDate, 0) <> 0 Then 'and If we have an end date also,
          strFilter = AppendFilter("([Publisher]  >= " & FORMAT(Me.StartDate, "\#mm\/dd\/yyyy\#") & _
          " AND [Publisher] <= " & FORMAT(Me.EndDate, "\#mm\/dd\/yyyy\#"))
       End If
    End If

    MsgBox "Filter to be " & strFilter   'Done so we know what will be sent, for debug purpses.

    If strFilter <> "" Then ' If there is a filter
       With Reports![rptTimmsImpact]
               .Filter = strFilter
               .FilterOn = True
        End With
    End If
    
    
    
'    If IsNull(Me.cboProject.Value) Then
'        strProject = "Like '*'"
'    Else
'        strProject = "='" & Me.cboProject.Value & "'"
'    End If

'    strFilter = "[Project] " & strProject & ""

'    With Reports![rptTimmsImpact]
'        .Filter = strFilter
'        .FilterOn = True
'    End With

'    If Me.chkCharity = -1 Then
'        strFilter = "([Charity] = True)"
'    ElseIf Me.chkCharity = 0 Then
'        strFilter = "([Charity] = False)"
'    End If

'    With Reports![rptTimmsImpact]
'        .Filter = strFilter
'        .FilterOn = True
'    End With

End Sub

More important you also need to add the code for AppendFilter so it knows what to do.

Mark_ · Mar 9, 2018

OK, did a bit more work and the attached should give you a better idea of how to do a lot of this. Some issues you need to be rather careful about are identifying EXACTLY what users have done/not done. If they enter a date then delete it, the field is not going to be NULL, same with PROJECT. It should be working much nicer now.

isladogs · Mar 9, 2018

I had a look at this as well a couple of hours ago & forgot to post it.
My version is slightly different so I thought I'd post it now.
Apologies for treading on Mark's toes ....

I've fixed lots of code errors in the original; removed the AppendFilter function which isn't needed and have done a few minor changes to improve the report layout.

The report is now only opened when the filter has been applied (or removed) which i think works better

I've also added Option Explicit on each code module & debugged it
You should ALWAYS DO THIS

Mark_ · Mar 9, 2018

@Colin,

I got in the habit a while ago of putting code I reuse into little functions like that. Not needed, but gives me one place to put errors instead of lots. B-)

GrahamUK33 · Mar 11, 2018

Looking at how the code works is interesting, I don’t think I would have ever have worked it out at the level I am at. Thank you very much guys, both examples work well.

GrahamUK33 · Mar 12, 2018

I have expanded on the code a bit to enable all the checkboxes. What I have managed to do is to search using only one checkbox with the code below.

What I am trying to achive is the ability of searching on any checkbox or a selection of checkboxes, I am wondering if I need a ‘Else’ or ‘And’ statement to get this working.

Code:

    If Me.chkCharity = True Then
       strFilter = AppendFilter("([Charity] = TRUE)", strFilter)
       Else
       If Me.chkEducation = True Then
          strFilter = AppendFilter("([Education] = TRUE)", strFilter)
          Else
          If Me.chkMagazine = True Then
             strFilter = AppendFilter("([Magazine] = TRUE)", strFilter)
             Else
             If Me.chkNewspaper = True Then
                strFilter = AppendFilter("([Newspaper] = TRUE)", strFilter)
                Else
                If Me.chkNHS = True Then
                   strFilter = AppendFilter("([NHS] = TRUE)", strFilter)
                   Else
                   If Me.chkParliament = True Then
                      strFilter = AppendFilter("([Parliament] = TRUE)", strFilter)
                      Else
                      If Me.chkEducation = True Then
                         strFilter = AppendFilter("([Education] = TRUE)", strFilter)
                         Else
                         If Me.chkBroadcast = True Then
                            strFilter = AppendFilter("([TV/Radio] = TRUE)", strFilter)
                         End If
                      End If
                   End If
                End If
             End If
          End If
       End If
    End If

isladogs · Mar 12, 2018

I see you are still using the AppendFilter code though it is totally unnecessary.
Suggest you look again at my simpler alternative.

I'm not sure why you are using a series of nested if statements.
However replacing with Select Case statements would be easier to code and faster in practice.

Try not to make code more complex than is necessary

GrahamUK33 · Mar 12, 2018

Thanks for replying.

I have looked through the code from the example you gave, I can only see that it works on one checkbox as below.

Code:

If Me.chkCharity = True Then
       strFilter = AppendFilter("([Charity] = TRUE)", strFilter)
End If

My knowledge of VBA is very limited, which is how I have come up with the code that I posted. I am not sure on how to convert the code into a Select Case statement to allow multiple checkboxes to be searched on.

isladogs · Mar 12, 2018

Apologies for two things:

1. The database I posted only partially removed the AppendFilter code
2. I hadn't realised you wanted to do multiple filters
So lets stick with AppendFilter
As you said, Select Case won't work as it only allows one output

Can you clarify what ticking multiple checkboxes will mean
e.g. if user ticks Charity, Education, NHS should the report only shows records where ALL of those filter apply? i.e. Charity AND Education AND NHS

OR where any of those apply i.e. Charity OR Education OR NHS

This will determine the way your code should be done

GrahamUK33 · Mar 12, 2018

ridders said:
Apologies for two things:
Can you clarify what ticking multiple checkboxes will mean
e.g. if user ticks Charity, Education, NHS should the report only shows records where ALL of those filter apply? i.e. Charity AND Education AND NHS

Someone may/or may not select the project they are interested in, and may/or may not select a date range.

Additional searching can be done by selecting either one or multiple checkboxes to display records that have only been checked. If none of the checkboxes have been selected, then all records should appear. If Charity is selected then only those records should appear, and if Charity and Education have been selected then the records for Charity and Education should appear.

isladogs · Mar 12, 2018

If I understand you correctly, you want examples like this:

strFilter = "" =>all records shown

strFilter = "PROJECT = 'MBRRACE-UK' AND [Published] Between #10/09/2017# And #03/14/2018# AND ([Charity] = TRUE OR [Education] = TRUE)"

strFilter = "PROJECT = 'LAMBS' AND ([NHS] = TRUE OR [Newspaper] = TRUE)"

strFilter = ([NHS] = TRUE OR [Newspaper] = TRUE)"

strFilter = "PROJECT = 'MBRRACE-UK'"

The OR filters will cause you issues the way your AppendFilter code is written as it is only designed to use AND

Code:

AppendFilter = Trim(pvFilter) & " AND " & Trim(pvAdd)

Mark_ · Mar 12, 2018

I am guessing you only want to include "checked" values, correct? If it is NOT checked it would by default be omitted?

If so, I've got an update for you.

The problem becomes when you mix "AND" and "OR" conditions. You would be prompting for "AND" conditions from project, start date, and end date. Check boxes become "OR" conditions and would be nested within their own parenthesis.

As such you end up with "[FieldA] = VALUE AND (FieldB = TRUE OR FieldC = TRUE)"

Mark_ · Mar 12, 2018

ridders said:
The OR filters will cause you issues the way your AppendFilter code is written as it is only designed to use AND

Code:

AppendFilter = Trim(pvFilter) & " AND " & Trim(pvAdd)

I had asked about that. Added an optional third for "Match" so you could specify AND or OR. In theory you could do more though I am at a loss for the moment as to what. Busy day so brain has turned into a rather thick custard that lacks caffeine.

GrahamUK33 · Mar 12, 2018

Thank you very much guys, you have both been extremely helpful.

Form filter for a Report (1 Viewer)

Registered User.

Well-known member

Longboard on the internet

Registered User.

Attachments

Longboard on the internet

Registered User.

Attachments

Longboard on the internet

Longboard on the internet

Attachments

MVP / VIP

Attachments

Longboard on the internet

Registered User.

Registered User.

MVP / VIP

Registered User.

MVP / VIP

Registered User.

MVP / VIP

Longboard on the internet

Attachments

Longboard on the internet

Registered User.

Similar threads

Users who are viewing this thread