How do I exlude records from my database if a checkbox is false?-CodePudding

I have this split form with some basic search functions based on comboboxes and search fields. Now I want to exclude the records where my checkbox chk_NonC = false.

The VBA-code I currently use to filter my record source qry_Administration:

    Function SearchCriteria()

    Dim Customer, CustomerLocation, CustomerLocationPlace, ExecutionDate, Material As String
    Dim Intern, Extern As String
    Dim task, strCriteria As String

    If Me.chk_AuditEX = True Then
        Extern = "[AuditEX] = " & Me.chk_AuditEX
    Else
        Extern = "[AuditEX] like '*'"
    End If
    
     If Me.chk_AuditIN = True Then
        Intern = "[AuditIN] = " & Me.chk_AuditIN
    Else
        Intern = "[AuditIN] like '*'"
    End If

    If IsNull(Me.cbo_CustomerLocations) Then
        CustomerLocation = "[CustomerLocationID] like '*'"
        CustomerLocationPlace = "[LocationCompanyPlace] like '*'"
    Else
        CustomerLocation = "[LocationCompanyName] = '" & Me.cbo_CustomerLocations.Column(0) & "'"
        CustomerLocationPlace = "[LocationCompanyPlace] = '" & Me.cbo_CustomerLocations.Column(1) & "'"
    End If
    
    
    If IsNull(Me.cbo_Customers) Then
        Customer = "[CustomerID] like '*'"
    Else
        Customer = "[CustomerID] = " & Me.cbo_Customers
    End If

    If IsNull(Me.txt_ExecutionDateTo) Then
        ExecutionDate = "[ExecutionDate] like '*'"
    Else
        If IsNull(Me.txt_ExecutionDateFrom) Then
            ExecutionDate = "[ExecutionDate] like '" & Me.txt_ExecutionDateTo & "'"
        Else
            ExecutionDate = "([ExecutionDate] >= #" & Format(Me.txt_ExecutionDateFrom, "mm/dd/yyyy") & "# And [ExecutionDate] <= #" & Format(Me.txt_ExecutionDateTo, "mm/dd/yyyy") & "#)"
        End If
    End If

    If IsNull(Me.cbo_Material) Or Me.cbo_Material = "" Then
        Material = "[MaterialID] like '*'"
    ElseIf Me.cbo_Material = 6 Then
        Material = "[MaterialID] in (" & TempVars!tempMaterial & ")"
    Else
        Material = "([MaterialID] = " & Me.cbo_Material & ")"
    End If

    strCriteria = Customer & "And" & CustomerLocation & "And" & CustomerLocationPlace & "And" & _
            & ExecutionDate & Material & "And" & Extern & "And" & Intern
                
    task = "Select * from qry_Administration where (" & strCriteria & ") order by ExecutionDate DESC"

    Debug.Print (task)
    
    Me.Form.RecordSource = task
    Me.Form.Requery
End Function

Now I want to add this new checkbox Non-Compliant named chk_NonC

When I set chk_NonC to true and press search I want my split-form to show all records.

When I set chk_NonC to false and press search I want my split-form to hide all records where Non_compliant is true

You can see it as a hide function for my database. If I set this checkbox to false then hide all records where non-compliant is set to true.

Please note that function SearchCriteria is called on the OnChange Events of the comboboxes or by clicking a search-icon on the top of my split-form.

CodePudding user response：

Just follow the same flow defined for the other controls.

Create the string portion for the compliance and append it to the rest of the sql script.

Dim strCompliant As String
strCompliant = IIf(Me.chk_NonC,"[Non_compliant]=True","[Non_compliant]=False")

strCriteria = Customer & " And " [...] & " And " & strCompliant

Keep in mind, you need spaces between the " And " joins in strCriteria.