Results 1 to 3 of 3
  1. #1
    srkempka is offline Novice
    Windows 7 32bit Access 2013 32bit
    Join Date
    Jun 2018
    Posts
    5

    Using a listbox on a search form to filter a query

    I know this is a relatively common issue, but I couldn't find an answer to my specific scenario...

    I have a functional search form with several text boxes and 2 combo boxes. Everything worked/works great except I was just asked to create multi-select functionality for my combo boxes. After looking for solutions, it seems that combo boxes aren't able to do that, but listboxes are. The issue is that no matter what/how many items I select, the Query I have acts as if the listbox is null so it returns everything.

    I'm not sure this makes a difference, but one of the fields (Type) has its data concatenated. I'm assuming the issue is stemming from my current SQL and my lack of changing it since converting the combo box to a list box. Here is the reduced form of my Query's SQL:

    SELECT [Consolidated Data].[Form Name], [Consolidated Data].[Type]

    FROM [Consolidated Data]



    WHERE (((Forms!SearchF![Product Form] Is Null) Or ([Form Name] Like "*" & Forms!SearchF![Product Form] & "*")) And ((Forms!SearchF!Type Is Null) Or ([Type] Like "*" & Forms!SearchF![Type] & "*")));


    Any thoughts/advice/recommendations?

    Thanks!

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    If [Product Form] is a name of a form control, it's rather misleading. Can't give specific advice because of that uncertainty ([Form Name] is not any better, I'm afraid) but can advise generally.
    First, you'll have to explain exactly how things should work unless you only need generalities. So generally, you'd have to loop through the listbox and concatenate all the values, separating with commas. Then concatenate this WHERE part to the SELECT ... FROM portion and do whatever it is you need to do with the resulting sql (open, run, pass it somewhere). The field you referred to is comma separated values? These are actually visible as such in a table but are not multi value fields? If so, they shouldn't pose a problem if they're concatenated with the other values and your sql contains an IN clause, as in WHERE myField IN (val1,val2,val3...) but they'd have to be added as the entire chunk.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Just adding to micron's excellent advice, do you really have 4 different forms open as well as the one with the listbox? If so, why?
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

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

Similar Threads

  1. Replies: 8
    Last Post: 03-05-2016, 10:39 AM
  2. Filter a form using a listbox
    By jrosen12 in forum Forms
    Replies: 3
    Last Post: 03-23-2015, 07:44 AM
  3. Replies: 7
    Last Post: 09-27-2014, 09:11 PM
  4. Replies: 13
    Last Post: 08-22-2013, 02:37 PM
  5. Filter Split Form using ListBox and .ME
    By clchris_80 in forum Access
    Replies: 3
    Last Post: 01-17-2013, 07:30 PM

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