Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2013

    Unanswered: Filter by multiple fields

    Hi -

    I am a relative novice to Access and I want to place a button on a form whereby I can filter that form by any one, two, or all, of three fields ("Rx_ID", "First name", and "Last Name"). What I want to allow is for a user to click a "Filter by..." button which activates a dialogue box that allows valid filter ctiteria to be entered into any one, two or all three fields - then to click "OK" and have the form filtered by whatever criteria has been set (or if the record - or set of records - does not exist, to return a "Sorry but no records exist based on those search criteria"). I can do this in FileMaker - but don't know how in Access...

    I have found the Me.Filter command...

    Private Sub FilterBy_Click()
    Me.Filter =
    End Sub

    ...but cannot find how to apply it to my situation

    "[Rx_ID] = " & "[First name] = " & "[Last Name] = "

    I know will I also need another button to turn the filter off - but at least the code for that is straightforward... (I hope)...

    Private Sub FilterOff_Click()
    Me.Filter = ""
    Me.FilterOn = False
    End Sub

    Any help or advice would be appreciated.

  2. #2
    Join Date
    Feb 2013
    Hi Rramjet,

    I was also looking for ways to apply several filters to a form through buttons, and I bumped into a possible solution here.

    I hope this can help you,


  3. #3
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    a filter in Access is effectively an SQL WHERE clause minus the word WHERE

    string / text columns must be enclosed in ' or "
    date columns must be enclosed in #
    ..both of these are so the SQL engine knows where the text or dtae literal starts and stops

    dates should be in ISO (YYYY MM DD) or US format (MM DD YYYY) with appropriate separators eg yyyy/mm or yyyy-mm dependign on the localisation of the computer

    so assuming that RX_ID is numeric (based on the suffix _ID), and lastname is string

    strfilter = "RX_ID >1000 AND LastName ='Rramjet'"
    as you have already identified having spaces in column/tabels neames requires you to use square brackets to tell the SQL engine where the column/table names starts and stops. I'd strongly recommend that in future you do nbot use spaces in column names. if want a human friendly label sith spacing/punctuation and so on set a value in the caption property of the column definition.

    as to how you set the values ther's several ways

    one is to put the relevant search fields in the forms header or footer
    another is to put it into a separate form

    to start with you'd probably be better off putting it in the forms header or footer (its easier to talk through)

    place your controls that will have the values you want to filter on. doesn't matter what they are text boxes, combo/list boxes and so on.
    place your apply filter command button (cmdFilter), give it a caption 'Apply Filter'
    I'd also suggest you add a button that clears the filter controls (cmdClear)
    lets say you have two filter controls txtSetRXIDFilter and txtSetNameFilter

    create some code behind the cmdfilter button. select the button, right click, build event, code

    you should see a code stub
    Private sub cmdFilter_Click()
    end sub
    inside that code stub we are going to place code that sets or clears the filter

    whilst we are at it force Access to require variables to be declared by putting Option Explicit immediately below Option Compare Database

    Private sub cmdFilter_Click()
    dim strFilter as string
    strFilter = '1 =1'  'a trick that forces the fitler to be valid
    'depending on the caption of this button we are either clearing a filter or applying a filter
    if cmdFilter.Caption = 'Apply Filter' then 'we are applying the filter
      if is Null(txtSetRXIDFilter) = false AND isnumeric (txtSetRXIDFilter) = true then 
        strfilter = ' AND RX_ID = ' & txtSetRXIDFilter
      if is Null(txtSetNameFilter) = false and strlen(txtSetNameFilter) >0 then 
        strfilter = ' AND LastName like "%' & txtSetNameFilter & '%"'
      if strlen(strFilter>3) then ' we need to apply a filter
        me.filter = strfilter
        cmdFilter.Caption = 'Remove Filter'
    'not to sure you may need to requery the form to activate the filter
    'if so
      else 'do nothing
    else 'we are removing the filter
      me.filter = ''
      me.filteron = false
      cmdFilter.Caption = 'Apply Filter'
    'not to sure you may need to requery the form to activate the filter
    'if so
    end sub
    also the code for the (Optional) 'clear filter' button
    [code]Private sub cmdClearFitler_Click()
    me.filteron = false
    cmdFilter.Caption = 'Apply Filter'
    'not to sure you may need to requery the form to activate the filter
    'if so
    end sub{/code]

    you can expand the scope fo the process if required

    ferisntance you could have a combo box and value for RXID where the combobox holds mathmatical operators such as = <> < > <= >= and use that when defining the filter for RX_ID
    similarly you coudl ue an operator for the string compariosn (ie =, starts with, ends with, or contains)

    having got the basis of the filter handling sorted out you coudl then put the data capture into a modal form (one that takes precedence over other forms) and then use that form to provide any validfation and when completed push the values to the form that needs the filter. but I'd leave that for another day
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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