Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2006
    Posts
    47

    Unanswered: Zero to Many Query Criteria from Combo/List Boxes without VBA

    I would like to have several Combo Boxes and List Boxes on a form that allow the user to select none to all of them and then run one to many queries that have the same fields.

    For example, it would have list boxes for States, Dates, Names and they can select 2 states and 1 date and no names.

    The problem I hit is that in the above example when they don't select a name the query then returns no records.

    I am aware of the possibility of using Query Def in VBA Code to potentially do this but that is far more complicated and time consuming then I would like to get into and I haven't had much experience with it.

    Additionally, I need to be able to have others make changes to this and no one else knows VBA Code.

    Thank you in advance for any help.

  2. #2
    Join Date
    May 2005
    Posts
    1,191
    Quote Originally Posted by rich8008
    I would like to have several Combo Boxes and List Boxes on a form that allow the user to select none to all of them and then run one to many queries that have the same fields.

    For example, it would have list boxes for States, Dates, Names and they can select 2 states and 1 date and no names.

    The problem I hit is that in the above example when they don't select a name the query then returns no records.
    You might want to check out my post in the Code Bank, it does almost exactly what you're wanting, but with VBA. It even can help with your returning no record problem.
    Quote Originally Posted by rich8008
    I am aware of the possibility of using Query Def in VBA Code to potentially do this but that is far more complicated and time consuming then I would like to get into and I haven't had much experience with it.

    Additionally, I need to be able to have others make changes to this and no one else knows VBA Code.
    I'm pretty sure this is not possible. If you want to be able to do all that, you need to use VBA. Contrary to popular belief, functionality of a dB does not simply appear; it needs to be programmed in. That's why the IT guys get paid the big bucks

    Bottom Line:
    If you want to do what you expressed above, then either you or someone else in your company is going to have to learn VBA, or you're going to have to outsource the work.
    Me.Geek = True

  3. #3
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Are you using 2 listboxes, 1 for each state selected? Does your criteria for state in your query then have something like (like Forms!MySearchForm!Statelistbox1 & "*" or like Forms!MySearchForm!Statelistbox2 & "*")? If you have 1 multi-select State listbox then Nick is right, you need to walk through the values in the listbox and construct a vba statement. Look at his example to do this.

    And then do you also have some date criteria in the same query matching against the date search value on the form?

    If you want to use that same query and return values if there is a blank name in the table (without writing vba), you can do an expression in the query for the name column (ie SName: iif(isnull(Forms!MySearchForm!MySearchNameField)," X",[NameField]) and then in the criteria for that column in the query, put... like Forms!MySearchForm!MySearchNameField or like "X"). It doesn't have to be "X". It could also be some other character.

    What you're doing is fooling the query by saying in the expression....If the Name search value on the form is null, show an "X" in this column (to show all values), otherwise return the value of [NameField]. And then in the criteria for that column you're saying... like the search field value for Name on the form or like "X" if no search value for Name is selected.

    You can also do the same method for the Date column in the query as you do for the Name column in the query, using an old date value in the expression which would never be entered.

    Here's an example of what I mean on how to do it in the query without building a vba statement (SearchForm and Query2). The only vba code in the SearchForm is to open the query but if you don't select a State in either one on the listboxes on the SeachForm, you won't get any returns (but you can make these like the Name field). But you can have blank Name values or blank date values in some of the records which is usually the problem, or leave these blank on the SearchForm and also return records. Look at the difference between Query2 and Query2x.

    I hope that is what you were getting at.
    Attached Files Attached Files
    Last edited by pkstormy; 08-04-07 at 03:10.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  4. #4
    Join Date
    Jan 2006
    Posts
    47

    Thank you very much!

    Thank you very much for both of these post.

    Both of them are extremely helpful!!!

Posting Permissions

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