Results 1 to 5 of 5
  1. #1
    Join Date
    May 2012

    Unanswered: Search form finds null values in specific fields determined by form?

    I want to create a search form where you check boxes that correlate to different fields in different tables. Then, I want to query to find all null values within the checked fields only. For instance, if the user checks name, then any records with a missing name will appear. If the user checks city and state, then users missing EITHER of those will appear (not both).

    I know I can put Is Null in the criteria of the query, but how do I make it dependent on which boxes are checked on the form?

  2. #2
    weejas is offline Grumpy old man (training)
    Join Date
    Sep 2006
    Surrey, UK
    Provided Answers: 17
    How do you want to display the results? That will affect which methods will work better for you.
    10% of magic is knowing something that no-one else does. The rest is misdirection.
    Beers earned: 2

  3. #3
    Join Date
    May 2012
    Probably just in a continuous subform where you can click it to view the full entry in another form and then edit the missing elements.

  4. #4
    Join Date
    May 2012
    Okay, I'm a fairly inexperienced Access user and I have yet to use the filter property. I'll ready up on it and then see if I can make some sense of the code you posted. Thanks for your help!

  5. #5
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    I'd use filters on the sub form.
    mind you I probably wouldn't use a subform at all. I'd use a continuous form
    put the parameter controls in either the header or footer. I'd probably want to use checkboxes as the control type
    put a command button adjacent to the parameter controls. that command button toggles to either apply or remove the filter

    then place some code behind the command button's on click event

    if MyCommandButton.caption = "Apply Filter" then 'we are setting a filter
      dim NewFilter as string 'will hold the text representation of the filter
      NewFilter = "" 'set our defualt value
      'go through each of the checkbox control
      if MyCheckbox1 = true then
        NewFilter = NewFilter & " isnull(MyColumn01) = true OR "
      if MyCheckbox2 = true then
        NewFilter = NewFilter & " isnull(MyColumn02) = true OR "
    ..and so on
      if MyCheckboxn = true then
        NewFilter = NewFilter & " isnull(MyColumnnn) = true OR "
      'OK we need to check the users has requested something
      'if they have then we need to chop off the trailing ' OR '
      'if they haven't then treat as no filter applied
      if len(NewFilter)>0 then the user has requested a search
        New Filter = left(NewFilter, len (NewFilter) -4)
        me.filter = NewFilter
        me.filteron = true
        MyCommandButton.caption = "Remove Filter" 'reset the caption
      'else 'don't need to do anything as the filter hasn't been applied so far
    else 'we are removing the filter
      me.filteron = false
      MyCommandButton.caption = "Apply Filter" 'reset the caption
    replace the words in italics with the relevant control / column names
    bear in mind the above is 'aircode' its not been tested, its not been verified, heck it may even have spelling mistakes in

    read up on the filter property
    I'd rather be riding on the Tiger 800 or the Norton

Tags for this Thread

Posting Permissions

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