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!
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.
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?
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.
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.
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?
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 ?
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.