Results 1 to 10 of 10
  1. #1
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228

    Filter query results through check boxes?

    Just a quick/easy one I hope.

    I want to apply a filter on a form based on check boxes. For example.. (I have nothing in testing/live)

    lets say I clicked a checkbox to "show completed" I want it to show any that have a completion date not null.

    Maybe referring to dates is too difficult? I can add checkboxes to each record that are ticked based on criteria.

    I'm just after Ideas before I step into this. Its making the event happen or making the query use if statements to reference a checkbox that I cant visualise.



    Just any general pointers or links to anything similar would help a lot.

    Andy.

  2. #2
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Something like
    If Me!ChkNullDate=True Then
    Me.Filter="Not IsNull(MyDate)"
    Else
    .....
    End If
    Me.FilterOn=True

  3. #3
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    code something like this


    if chkboxcompleted then me.filter="CompletionDate is null"

  4. #4
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228
    I've never done filtering this way. Appreciate the responses. Ill give it a go.

    Thanks!

  5. #5
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228
    I'm putting this code on the "on click" event of the checkbox, is this right? (I'm thinking not that's why I'm asking). Just to get my head around it..

    Code:
     if chkboxcompleted then me.filter="Date_Completed is null"
    chkboxcompleted = checkbox name
    Date_Completed = field name

    I was having syntax error but that's sorted now. (so it can see the code.. I was sceptical haha).

    Now its reading the code its filtering out any "completed" no matter if the box is checked or not. ( I have a refresh/requery event on the checkbox to be sure.)

    I don't understand how this code manipulated the form query, I'm sure once I do ill be fine.

    Andy

  6. #6
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    sorry, thought you know the principle of filtering with code which is

    me.filter=...
    me.filteron=true 'to apply the filter

    So add the second line to your code

  7. #7
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228
    Brilliant. I'm understanding better.

    Code:
    Private Sub ChkNullDate_Click()
    If Me!ChkNullDate = True Then
    Me.Filter = ""
     Else
     Me.Filter = "IsNull(Date_Completed)"
     End If
    Me.FilterOn = True
    Me.Requery
    Me.Refresh
    End Sub
    Then on the form load I apply the filter:

    Me.Filter = "IsNull(Date_Completed)"

    I'm sure there is a more elegant solution but I'm happy this is working. Thanks a lot.

  8. #8
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228
    as a matter of interest, how many records do you think it would take to make this form/code perform slowly?

    say its applying a filter to 10000 records, do you think it will be an issue?

  9. #9
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228
    Hi Ajax, Just seen this.

    I've never done it this way before. Usually just at query level. Thanks a lot for the help.

  10. #10
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    as a matter of interest, how many records do you think it would take to make this form/code perform slowly?

    say its applying a filter to 10000 records, do you think it will be an issue?
    all depends on what the form is doing with data and whether the fields you are filtering on are indexed, whether you are using massive rowsources for comboboxes/listboxes, using lookups in table fields etc. Under normal circumstances I would not think 10000 records would show an appreciable difference - a couple of seconds perhaps.

    For my search forms I have an unbound form with a bound subform to contain the returned data. And I don't fetch data for the subform until some filtering has been applied - that way perhaps only 50 records are passed across the network rather than 10,000. And rather than applying a filter to the subform recordset (which is what you are doing) I apply a criteria to the subform recordsource so only records I am interested are fetched.

    subform recordsource to return no records - SELECT * FROM myTable WHERE False

    using your example, to return records, change recordsource to - SELECT * FROM myTable WHERE Date_Completed is Null

    Note that is Null is faster than the isNull function because it is native to SQL, isNull is a VBA function which has to be applied to each record. Probably won't have much effect on performance, but why use a slower method anyway?

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 06-15-2017, 12:12 PM
  2. Filter Form with Multiple Check Boxes
    By Njliven in forum Forms
    Replies: 8
    Last Post: 01-09-2013, 01:50 PM
  3. Datasheet Filter with Check Boxes
    By EddieN1 in forum Forms
    Replies: 1
    Last Post: 05-17-2012, 06:53 PM
  4. How to Query fields with check boxes?
    By JynxRD in forum Queries
    Replies: 2
    Last Post: 09-10-2010, 08:35 PM
  5. Filter by Form: Check Boxes.
    By tbh7x in forum Forms
    Replies: 0
    Last Post: 08-18-2010, 09:15 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums