searching with multiple criteria from a form

[Guest]

I am trying to create a search form that will retrieve records displayed on another form. What I want is a form that has several fields and one button. The user should be able to enter as much information into the fields as he/she knows (leaving some fields blank) and click "search." The query should take into consideration the data in the fields and disregard fields that are left blank. I have gotten to where the macro goes field by field and accepts or disregards the field as criteria, but it runs a new query for each field instead of quering the filtered records. For instance, if two fields have information in them, I need the first query to run and then the second to filter the records returned from the first query. As it is right now, the second query filters all the records instead of the first filter results. This is sort of confusing, but I am about desperate. There may be better way to do it all together, but I don't know what it is . . . PLEASE HELP!

 

[Steve Schapel]

Crystal,

It is best to just use one query. In the criteria of all the fields in
the query where you are entering search criteria in the textboxes on the
form, put a reference to the form control, using syntax such as...
[Forms]![YourForm]![YourTextbox] Or [Forms]![YourForm]![YourTextbox] Is Null
(Note that after you save this query, if you open it again in design
view, you will see that Access has changed it around to suit its own
purposes... don't worry about this, it will be right!)

 

[Guest]

Steve
I don't understand. If I put criteria as "... Is Null" then it will return records where that field is blank. What I want is two forms
Form One
text boxes with labels that say "Customer", "manufacturer", "serial number", "date received", etc
button with label that says "Search

The user should be able to enter any known information. e.g. customer & manufacturer or customer & date receive
When the "Search" button is clicked, form two should open

Form Two
retrieved records based on info provide

I hope this is more clear. Please e-mail me at (e-mail address removed) if you have/need any more info. Thanks in advance for any help you can give

----- Steve Schapel wrote: ----

Crystal

It is best to just use one query. In the criteria of all the fields in
the query where you are entering search criteria in the textboxes on the
form, put a reference to the form control, using syntax such as..
[Forms]![YourForm]![YourTextbox] Or [Forms]![YourForm]![YourTextbox] Is Nul
(Note that after you save this query, if you open it again in design
view, you will see that Access has changed it around to suit its own
purposes... don't worry about this, it will be right!

 

[Steve Schapel]

Crystal,

I think I understood your question, and the answer I gave before is
correct. Did you try it?

Your statement "If I put criteria as '... Is Null' then it will return
records where that field is blank" is wrong. It will not, and indicates
you did not try to apply my earlier advice. It will return records that
match the search criteria entered for that field, if there is a criteria
entered, and it will return all records if there is no search criteria
entered.