Results 1 to 4 of 4
  1. #1
    johnrohan is offline Novice
    Windows 10 Access 2007
    Join Date
    Aug 2017
    Posts
    15

    Expanding the use of a filter button on a form

    Hello, you all have been fantastic and very helpful before, so I'm here to ask another question. Here's a question that probably has a very simple answer, but it eludes me.



    OK, I have a subform datasheet with a "Thing" field that previously let you choose values from a dropdown menu from a limited list of options, lets call them "Thing1", "Thing2", etc. At the top of the form, I added several filter buttons for it. So, for instance, I hit the "Thing1" filter button and it only displays the records with "Thing1". I used an apply filter macro for this, and it worked perfectly. So far so good.

    But now conditions have changed. Previously, I did not allow multiple values. But now I have to take into account that a record might have both a "Thing1", "Thing2", and "Thing3", etc, not only one of them. So I changed the field to a list box drop down menu instead. And this works fine. However, now when I try to use the filter button, I get the error message: "The multi-valued field "[Thing]" cannot be used in a WHERE or HAVING clause."

    So my question is, how can I set the macro so the filter button will show ANY records with that value? So for example, I hit the "Thing2" button, and it displays all records that have "Thing2", regardless of the other Things it might have? I tried playing with AND and OR functions and I couldn't get it right. For simplicity, I would prefer to keep using macros instead of vb for this particular function, if possible.

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    I am a bit confused. First you say you have a "Thing" field then you say "a record might have both a 'Thing1', 'Thing2, and 'Thing3'". This suggests 3 fields because that is the only way a record can have any or all 3 values. So do you have 1 field or 3 fields?

    If this really is 1 field and you want to apply multiple filter criteria on that 1 field, have to use IN clause. Use a listbox and VBA to construct. Review http://allenbrowne.com/ser-50.html.

    A multi-select combobox can only be used to save multiple values to a multi-value field and cannot be referenced as parameter for WHERE clause.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    johnrohan is offline Novice
    Windows 10 Access 2007
    Join Date
    Aug 2017
    Posts
    15
    Quote Originally Posted by June7 View Post
    I am a bit confused. First you say you have a "Thing" field then you say "a record might have both a 'Thing1', 'Thing2, and 'Thing3'". This suggests 3 fields because that is the only way a record can have any or all 3 values. So do you have 1 field or 3 fields?
    I beg to differ. It's only one field. You can have multiple values, because under the lookup tab, I set the "Allow Multiple Values" property to "yes". So you can choose multiple values for the field, if you have a list box that allows you to check more than one value. In the table, they are separated by commas.

    If this really is 1 field and you want to apply multiple filter criteria on that 1 field, have to use IN clause. Use a listbox and VBA to construct. Review http://allenbrowne.com/ser-50.html

    I was afraid that might be the answer. I was hoping to keep it as a macro. I will try to use the examples on that site, but that's a little complex for me.

    A multi-select combobox can only be used to save multiple values to a multi-value field and cannot be referenced as parameter for WHERE clause.
    Right, that's what the system is telling me. But I was hoping there was a simple way I could work around that.

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    I NEVER use multi-value fields. Access is really storing the data in a hidden table. That hidden table has a record for each selected value. So yes, as you say, 1 field. Sorry for my overly generalized statement.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. form with expanding/shrinking sub-forms?
    By benjammin in forum Forms
    Replies: 19
    Last Post: 02-02-2015, 02:52 PM
  2. Replies: 3
    Last Post: 11-27-2012, 07:20 AM
  3. Replies: 28
    Last Post: 03-08-2012, 06:47 PM
  4. Expanding a form's data fields
    By hertfordkc in forum Forms
    Replies: 6
    Last Post: 07-18-2011, 12:33 PM
  5. Expanding Memo Field in Form
    By AKQTS in forum Forms
    Replies: 3
    Last Post: 07-26-2010, 08:51 AM

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
  •  
Other Forums: Microsoft Office Forums