Results 1 to 10 of 10

Thread: Checkbox filter

  1. #1
    Join Date
    Sep 2011
    Posts
    33

    Unanswered: Checkbox filter

    Hello, back again.

    As I've said before I'm reasonably new to access so this is probably a stupid question, I imagine this is simple enough for someone who knows what they are doing.

    Basically what want to do is to be able to filter a particular field in a report through an unbound checkbox in a form. The fields data type is a number:

    1 = SiteTypeA
    2 = SiteTypeB
    3 = SiteTypeC

    I currently run a report which will always have SiteTypeA & SiteTypeB, I would like to use the checkbox to include SiteTypeC when checked, and when unchecked to exclude SiteTypeC. Currently I go into the query and put in the criteria <>3 to exclude and am finding it annoying having to change it all the time, and most of the users don't know how to do that.

    I open the report with DoCmd.OpenReport if that makes a difference.

    Thanks heaps for any help with this.

  2. #2
    Join Date
    Oct 2009
    Posts
    204
    =Iif([Forms]![yourform]![checkbox]="yes",[fieldname],<>"SiteTypeC")

    [fieldname] is the name of the field in your query that holds the SiteTypes. Change that expression to fit your field names and place it in the criteria for that field.

  3. #3
    Join Date
    Sep 2011
    Posts
    33
    Thanks for replying.

    However, that did not work. When I enter that in the query and then preview the report, it comes up blank. When I ran the report from the form I get:

    Run-time error '3071'

    The expression is typed incorrectly, or it is too complex to be evaluated...

    Do you know what I'm doing wrong? I followed the instructions you gave.

    Thanks again for replying.

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    I would use the wherecondition argument of DoCmd.OpenReport. In your code to open the report, if the checkbox is unchecked add a wherecondition of:

    "FieldName <> 3"

    More info on that argument here:

    Open a second form to the record
    Paul

  5. #5
    Join Date
    Sep 2011
    Posts
    33
    Thank Pbaldy

    Although I'm going to be a real pain as I know nothing about VBA. I didn't create this database, I've just taken over it from someone else who know what they were doing.

    There is a fair bit of code behind the report because there are about 5 filters on the form and so the DoCmd looks like this

    Code:
    DoCmd.OpenReport "rptScheduleSummary", acViewPreview, , strCriteria
    Do I just put it at the end of that? Everytime I change something I get an error, probably cause I have no idea what I'm doing. I've downloaded VBA for dummies so hopefully this will give me some sort of an idea.

    Thanks for your help.

  6. #6
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    No, strCriteria is the wherecondition I mentioned, so you would incorporate the checkbox code into the code that builds that string. What's the code that does that?
    Paul

  7. #7
    Join Date
    Sep 2011
    Posts
    33
    Ok, this is all the code in there. I can't wrap my head around this stuff.

    Code:
    Option Explicit
    Option Compare Database
    
    Dim strCriteria As String
    Dim dteReportStart As Date
    Dim dteReportEnd As Date
    
    _______________________________________________
    
    Private Sub btnClearFilter_Click()
    
        Me!txtFilterFinancialYear = Null
        Me!txtFilterFinancialYearQuarter = Null
        Me!cboFilterZone = Null
        Me!cboFilterElectorate = Null
        Me!cboFilterServiceStyle = Null
        Me!cboFilterStatus = Null
            
    End Sub
    
    ____________________________________________________
    
    Private Sub btnScheduleSummary_Click()
        Call setCriteriaDate
        If strCriteria = "No Criteria" Then
            strCriteria = ""
        Else
            strCriteria = "[CentreDevelopmentDateEnd] >= #" & Format(dteReportStart, "mm/dd/yyyy") & "#" & _
                        " and [CentreDevelopmentDateEnd] <= #" & Format(dteReportEnd, "mm/dd/yyyy") & "#"
        End If
        
        If Not IsNull(cboFilterZone) Then
            If strCriteria <> "" Then
                strCriteria = strCriteria & " AND "
            End If
            strCriteria = strCriteria & "[CentreZone] = '" & cboFilterZone & "'"
        ElseIf Not IsNull(cboFilterElectorate) Then
            If strCriteria <> "" Then
                strCriteria = strCriteria & " AND "
            End If
            strCriteria = strCriteria & "[CentreElectorateFederal] = '" & cboFilterElectorate & "'"
        End If
        
        If Not IsNull(cboFilterServiceStyle) Then
            If strCriteria <> "" Then
                strCriteria = strCriteria & " AND "
            End If
            strCriteria = strCriteria & "[CentreServiceStyle] = " & cboFilterServiceStyle
        End If
        
        If Not IsNull(cboFilterStatus) Then
            If strCriteria <> "" Then
                strCriteria = strCriteria & " AND "
            End If
            strCriteria = strCriteria & "[CentreStatus] = " & cboFilterStatus
        End If
        
        DoCmd.OpenReport "rptScheduleSummary", acViewPreview, , strCriteria
    
    ______________________________________________
    
    Sub setCriteriaDate()
        
        strCriteria = ""
        dteReportStart = 0
        dteReportEnd = 0
        
        If IsNull(txtFilterFinancialYear) Then
            strCriteria = "No Criteria"
            Exit Sub
        End If
        
        If txtFilterFinancialYearQuarter = "1st" Then
            dteReportStart = DateSerial(Val(txtFilterFinancialYear) - 1, 7, 1)
            dteReportEnd = DateSerial(Val(txtFilterFinancialYear) - 1, 9, 30)
        ElseIf txtFilterFinancialYearQuarter = "2nd" Then
            dteReportStart = DateSerial(Val(txtFilterFinancialYear) - 1, 10, 1)
            dteReportEnd = DateSerial(Val(txtFilterFinancialYear) - 1, 12, 31)
        ElseIf txtFilterFinancialYearQuarter = "3rd" Then
            dteReportStart = DateSerial(Val(txtFilterFinancialYear), 1, 1)
            dteReportEnd = DateSerial(Val(txtFilterFinancialYear), 3, 31)
        ElseIf txtFilterFinancialYearQuarter = "4th" Then
            dteReportStart = DateSerial(Val(txtFilterFinancialYear), 4, 1)
            dteReportEnd = DateSerial(Val(txtFilterFinancialYear), 6, 30)
        Else
            dteReportStart = DateSerial(Val(txtFilterFinancialYear) - 1, 7, 1)
            dteReportEnd = DateSerial(Val(txtFilterFinancialYear), 6, 30)
        End If
        
    End Sub
    
    ______________________________________
    
    Private Sub btnExit_Click()
        DoCmd.Close
    End Sub

  8. #8
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    In the sub:

    Private Sub btnScheduleSummary_Click()

    you want to add another block right before the DoCmd.OpenReport line. You can copy the block that tests cboFilterStatus and adjust the test and filter for your checkbox and site field. It would look something like this:

    Code:
        If Me.CheckBoxName = False Then
            If strCriteria <> "" Then
                strCriteria = strCriteria & " AND "
            End If
            strCriteria = strCriteria & "FieldName <> 3"
        End If
    Paul

  9. #9
    Join Date
    Sep 2011
    Posts
    33

    Thanks!

    Unreal, it works great.

    Thanks so much for spelling it out for me, greatly appreciated.

  10. #10
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    No problemo! Next time you're getting your hands dirty though.
    Paul

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •