Add Filters to Form

[anma]

Hi All,

I have found many posts about Queries, however I don't seem to be able to find one solves my problem!

I have a large database which I need users to to be able to filter similar to a form filter.

To solve this I have created a form with four Combo boxes.

What I would like to have is that if the user enters none, one or many search options into the combo box the results are true.

At present I have played with "Is Null" however if filter one for example has five results, and then "Is Null" on a second filter is used then it would display all (1000)
What I think I actually need is a query that ignores the question if empty.(preferably access as I am not experienced in other scripts, but willing to give it a go.)

Hope that makes sense!

Anyone's help and time would be grateful.

Andy

[jimatqsi]

Andy,
You don't say, but I assume you are working from a form, you want to filter data dynamically on a form. If that is the case, maybe this example of something I did will help.

I made a form to show shipping information. I put some text boxes and check boxes at the top of the form. Every time one of those objects changes, the After_Update event causes the form to be refiltered. You can see in this code that the FilterForm() function is called whenever one of those boxes is updated.

Look at the code in the FilterForm function. It creates a SQL query string based on the various filter options available to the user. If any of the filter options are not used, they are ignore in the criteria.

Hope that helps.

Jim

Option Compare Database
Option Explicit
 
Private Sub FilterForm()
Dim strSQL As String
 
Dim strCriteria As String
 
strSQL = "SELECT CDate(nz([dtmDateShip],Date())) AS Shipdate, dtmDateShip, strShipVia, strShipViaServices, strOrderNumber, strShipmentStatus, strInvoiceID, intPackageCount, "
strSQL = strSQL & " dtmTimeStamp, format(dtmTimeStamp,'MM/DD/YY') as PrDate, curShipmentAddon "
strSQL = strSQL & " FROM tbl_OEShipping "
 
strCriteria = " where 1=1 "
 
If Me!chkUPSOnly Then
    strCriteria = strCriteria & " and instr(strShipvia,""" & "UPS" & """)>0"
End If
 
If Me!chkShippedFilter <> Me!chkUnshippedFilter Then
    If Me!chkUnshippedFilter = True Then
        strCriteria = strCriteria & " and NZ(strShipmentStatus)<> """ & "Shipped" & """ "
    End If
 
    If Me!chkShippedFilter = True Then
        strCriteria = strCriteria & " and NZ(strShipmentStatus)= """ & "Shipped" & """ "
    End If
 
End If
 
    If Nz(Me!txtFilterDate, "") <> "" Then
        strCriteria = strCriteria & " and dtmDateShip=#" & Me!txtFilterDate & "# "
    End If
 
    Dim strDate As String
    strDate = Format(Me!txtFilterDatePrinted, "MM/DD/YY")
 
    If Nz(Me!txtFilterDatePrinted, "") <> "" Then
        strCriteria = strCriteria & " and format(dtmTimeStamp,'MM/DD/YY')=""" & strDate & """ "
    End If
 
    DoCmd.Hourglass True
    Me.Form.RecordSource = strSQL & strCriteria & " ORDER BY CDate(nz([dtmDateShip],Date())) DESC , strShipVia, strOrderNumber;"
    Me.Requery
    DoCmd.Hourglass False
 
End Sub
 
Private Sub chkShippedFilter_AfterUpdate()
    FilterForm
 
End Sub
 
Private Sub chkUnshippedFilter_AfterUpdate()
    FilterForm
 
 
End Sub
 
Private Sub Text20_DblClick(Cancel As Integer)
 
End Sub
 
Private Sub chkUPSOnly_AfterUpdate()
    FilterForm
End Sub
 
Private Sub strOrderNumber_DblClick(Cancel As Integer)
    Dim strOrder As String
    strOrder = Me!strOrderNumber
   DoCmd.Close
   Forms!frm_OEShippingSelectCustomer!txtSelectedOrder = strOrder
 '  Forms!frm_OEShippingSelectCustomer.txtSelectedOrder_AfterUpdate
 
End Sub
 
Private Sub txtDateShip_DblClick(Cancel As Integer)
    Me!txtFilterDate = Me!txtDateShip
    FilterForm
 
End Sub
 
Private Sub txtFilterDate_AfterUpdate()
    FilterForm
 
End Sub
 
Private Sub txtFilterDatePrinted_AfterUpdate()
    FilterForm
 
End Sub
 
Private Sub txtPrintDate_DblClick(Cancel As Integer)
    Me!txtFilterDatePrinted = Me!txtPrintDate
    FilterForm
End Sub
Private Sub cmdClose_Click()
On Error GoTo Err_cmdClose_Click
 
 
    DoCmd.Close
 
Exit_cmdClose_Click:
    Exit Sub
 
Err_cmdClose_Click:
    MsgBox Err.Description
    Resume Exit_cmdClose_Click
 
End Sub
 

[NeoPa]

Andy, please check out Example Filtering on a Form. It has an Example database attached that you can play with.

Let us know if this helps you solve your issue or whether more assistance is required.

Welcome to Bytes!

[anma]

Neopa

Thank you for your help, it is exactly the type of idea I was looking for.

Just didn't have the knowledge.

One thing is your example works with multiples or individuals, however I can not get mine to do the same.

My Event Procedure looks something like this:

 
Private Sub Product_Change()
    Call CheckFilter
End Sub
 
Private Sub Container_Change()
    Call CheckFilter
End Sub
 
Private Sub CheckFilter()
 
    Dim strFilter As String, strOldFilter As String
 
    strOldFilter = Me.Filter
    'Combobox1
    If Me!Product > "" Then
        strFilter = strFilter & _
                    " AND ([Product Type] Like '" & _
                    Me!Product & "*')"
    'Combobox2
    If Me!Container > "" Then
        strFilter = strFilter & _
                    " AND ([Container Number] Like '" & _
                    Me!Container & "*')"
    'Debug.Print ".Filter = '" & strOldFilter & "' - ";
    'Debug.Print "strFilter = '" & strFilter & " '"
    'Tidy up results and apply IF NECESSARY
    If strFilter > "" Then strFilter = Mid(strFilter, 6)
    If strFilter <> strOldFilter Then
        Me.Filter = strFilter
        Me.FilterOn = (strFilter > "")
 
    End If
    End If
    End If
End Sub
 
 

I have had to add extra "End If" as I was prompted to do so by access. Not sure if that is one of the problems.

Thanks for your help,

Andy

[jimatqsi]

Andy,
You want your first End If to be before the test for ComboBox2. Otherwise you don't filter anything unless you are filtering by Product. Similarly, I would move the 2nd End If to be before the 3rd If test, and the 3rd End If before the 4th If test. I don't think you really intend to nest any of these If tests.

Also, I like to initialize strFilter with "strFilter = "1=1 ". That way I don't have to worry about stripping the "and" of of the first criteria that may or may not have been added to the filter string.

Jim

[NeoPa]

It looks like you've missed out the underscores (_) from the If lines at line #16 and #21. These should read respectively :

If Me!Product > "" Then _
'and
If Me!Container > "" Then _

This ensures the following line is treated as a continuation. In this case a single line If statement requires no End If

Clearly with this fixed you can remove the lines #34 and #35. These currently skew the whole logic completely.

[anma]

Fantastic!

Thank you all so much.

It works perfectly,

Regards,

Andy

[NeoPa]

It's been a pleasure Andy :)