Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2012
    Posts
    3

    Unanswered: Setting up query for multi-field search box

    Hey all, I'm trying to get a query set-up for my search box. I'd like to have it so the user could enter any information into the form's text boxes and it'll pop out any relevant information. So the criteria I'm using for each field is

    Like [Forms]![SearchForm]![TextBox] & "*"

    Now that will give me all the relevant information based on whatever I enter but it excludes any table entries where there is null values. I would like to leave the values as null due to the nature of my database. Therefore, I would like for when a field is left blank in the form, it will show all the entries in the field even the ones with null values. Any help will be greatly appreciated.

    Thanks in advance!

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6

  3. #3
    Join Date
    Feb 2012
    Posts
    3

    Hurm

    So I'm not sure if that's what I'm looking for. My brain is getting fried though so I could be missing something critical.

    I'm trying to create a search form for a single table. It has 9 different fields but some of the records won't have a value for every field. From what I can tell this means that they'll have null values. I'd like all the fields to be able to be used for the search but not required to be completed for the query to be run. (Wild card?)

    From what I can tell, I can set up the criteria so I can enter any part of the each field and it will display the records that match. All the records except for ones that contain a null value in one of the fields from the table.

    Is there any way to show these?

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    In the WHERE clause, simply add OR xxx Is Null. If the criteria is something like:
    Code:
    WHERE LastName LIKE 'S*'
    It becomes:
    Code:
    WHERE LastName LIKE 'S*' OR LastName Is Null
    Have a nice day!

  5. #5
    Join Date
    Feb 2012
    Posts
    3

    I'm dumb

    So I tried messing around with the SQL but I'm really a novice when it comes to programming languages. I ended up adding the 'xxx Is Null' and it works for a single entry. What do I do if I want multiple fields to work if they're left empty? I tried adding 'And xxx2 is Null" as well as 'Or xxx2 Is Null' but neither work the way I'd like.

    The 'And' will just remove both fields from the query results and the 'Or' will show both fields with the null values but neglect the first field box that actually has information entered.

    Here's how the SQL is set up at the moment:

    WHERE (((Inventory.Manufacturer) Like [forms]![edit_inventory].[mnf] & "*") AND ((Inventory.[Kit/Component]) Like [forms]![edit_inventory].[kit] & "*") AND ((Inventory.Quantity) Like [forms]![edit_inventory_qnt] & "*") AND ((Inventory.[Lot #]) Like [forms]![edit_inventory].[lot] & "*") AND ((Inventory.[Expiration Date]) Like [forms]![edit_inventory_exp] & "*")) OR (((Inventory.[Lot #]) Is Null)) OR (((Inventory.[Expiration Date]) Is Null));

    Manufacturer being a field that will always have a value in my table but the others containing the blank records.

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so buidl the SQL ont he fly and then pass it to whatever

    Code:
    strSQL = "select my, column, list, from mytable"
    strWhereClause = " Where 1=1" 'a workaround to make certain you alwasy have a valid where clause
    if not isnull(mytextcontrol) then
      strwhereclause = strwhereclause & " and acolumn like '*" & mytextcontrol.value & "*'"
    endif
    if notisnull(mynumericcontrol) and isnumeric(mynumericcontrol) then
      strwhereclause = strwhereclause & " and bcolumn =" & mynumericcontrol.value
    endif
    strSQL = strSQL & strWhereclause & " order by a,sort,corder"
    note the leadign sapce before each line in the SQL
    eg ' where....', ' and......'
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14

    Wink You're not dumb

    Use parentheses for changing the default evaluation order of the expression.

    - If you want to evaluate several criteria simultaneously (i.e. all conditions must be met):
    Code:
    WHERE ( (xxx1 Like 'x*' OR xxx1 Is Null) AND (xxx2 Like 'y*' OR xxx2 Is Null) )
    - If you want to evaluate one criteria among several (i.e. at least one condition must be met):
    Code:
    WHERE ( (xxx1 Like 'x*' OR xxx1 Is Null) OR (xxx2 Like 'y*' OR xxx2 Is Null) )
    Have a nice day!

Posting Permissions

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