Results 1 to 13 of 13
  1. #1
    Join Date
    Jun 2002
    Location
    Kansas City
    Posts
    103

    Unanswered: Making query criteria conditional.

    I'm allowing users to input one or more search criterians (multiple fileds) on a form which is then updated to a table. These fileds are then referenced in the criteria section of a query. However, when any of the user inputed search criteria is null, the query obviously returns nothing. Is there a way to omit/ignore the critera in the query when it's null? I.E. the user wants to return a list of all employees in a particular job but does not want to specify any other criteria such as department, manager, etc. The last two fields are left blank and thus null when updated to my search table. Any suggestions for a workaround would be greatly appreciated!

  2. #2
    Join Date
    Feb 2002
    Posts
    2,232
    Why are you storing the criteria in a table ? Anyway, you can use * as your default value for a field that does not have a value entered by a user. This will pull back all records as long as no nulls exist in the record. If nulls are possible, you will have to test for them in your criteria.

  3. #3
    Join Date
    Jun 2002
    Location
    Kansas City
    Posts
    103
    Thanks for the response. By not referencing the form field(s) directly in the criteria section of the query, I was able to eliminate getting a "prompt" for data when the field is left blank on the form by the user. Updating a table with the user input fields (search criteria) and then referencing that table/field(s) in the query eliminates the promt.

    I don't quite understand how to use * (Like) in my query? I had been experimenting with it. For example, if the user selects a job title as the search criteria but leaves the department field blank on the form.......... I tried referencing the job title field in the criteria section under the job title column and referencing department (which is null) in the criteria section under the department column and placing * in the "OR" underneath criteria. This doesn't seem to work though?
    Last edited by JamesB; 06-24-02 at 14:59.

  4. #4
    Join Date
    Feb 2002
    Posts
    2,232
    In the fields that are left blank intentionally on the form, put * - Does it return the information you want ? If so, then just put a default value in your table that keeps the criteria and use * as the default value. When the criteria is saved, an asterisk will be inserted and you can use that in your query.

  5. #5
    Join Date
    Jun 2002
    Location
    Kansas City
    Posts
    103
    When I place an * directly in the criteria section of one column in the query and and reference the form field in the criteria section of another column, I get the desired records. However, I'm unable to make this work when I enter * or "*" or Like "*" on the form or in my search table and reference the form field or table field in the criteria section of my query. It certainly seems like it should work.

  6. #6
    Join Date
    Feb 2002
    Posts
    2,232
    Can you post the mdb or the query ?

  7. #7
    Join Date
    Feb 2002
    Posts
    2,232
    James,

    I received your message and understand. Just send me a simple version of what you are trying to do using pubs/northwind or some other bogus db that mimics your functionality.

  8. #8
    Join Date
    Jun 2002
    Location
    Kansas City
    Posts
    103
    Well, I now understand what's happening when using the wildcard. Your suggestion works perfect when I go directly to my query. The query returns the data correctly when I pull the my first data value (criteria) from the form field and then insert an * in the criteria section of a second column. However, when I try to pull the */wildcard from the from field, it interprets it literally. For example, when you type an * in the query and tab off, it defaults to Like "*" but on the form it treats it like a data value. So, I've been trying to fugure out some kind of workaround. Maybe with VB, I could determine whether a criteria field is left blank by the user and then insert an * directly in the criteria section of my particular column in the query? Maybe there's an easier way?

  9. #9
    Join Date
    Feb 2002
    Posts
    2,232
    When you refer to vb - do you mean vba or are you using vb as your front end and access as your back end ?

  10. #10
    Join Date
    Jun 2002
    Location
    Kansas City
    Posts
    103
    Wasn't very clear - VBA. I'm not a programmer. I've been referencing my Macro/VBA handbook.

  11. #11
    Join Date
    Feb 2002
    Posts
    2,232
    Are you using vb in access or are you creating a visual basic application ? Are you able to post a simplified version of what you are trying to do - because I have been able to create a prototype based on your descriptions and it works fine, but the reality may be you are trying to do something very different ?

  12. #12
    Join Date
    Jun 2002
    Location
    Kansas City
    Posts
    103
    I'm using a "little" VB in Access. I'll try to post a sample today. However, I was reviewing some other posts (Wildcard). I found a suggestion and have been experimenting with it. In the criteria section of the columns in the query, they suggested the following:

    =[Forms]![LookupForm]![CostCenter] Or ([Forms]![LookupForm]![CostCenter] Is Null)

    So far, it seems to work with three columns/criterians, but when I add the fourth, it fails. Also, Access does some "wierd" things with the critera once the query is saved, closed, and re-opened.

  13. #13
    Join Date
    Jun 2002
    Location
    Kansas City
    Posts
    103
    The solution in my last update works fine with only three wildcard fields, so I just eliminated one of the fields. Thanks rnealejr for taking the time to reply (a number of times).

Posting Permissions

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