Multiple Queries and Null Drop Downs

[Sirkevin]

Please could someone help with the following.

I am trying to get my query to work from a set of criteria set by drop downs in a form. I want it to work if any of the drop downs is left blank (null values). If any one of the drop down criteria is incorrect (i.e. no exact match) no records will be shown, all data must match to pull the records. In saying this I would like the records to be shown if 1 drop down is chosen or similarily 8 drop downs are entered (i.e. more accurate filtering).

I have pulled some of the data from the database I am designing and attached it below so hopefully it wil lmake more sense. Qrymain1 is the query (check the SQL for what I have done so far) and Searchform2 is the form supplying the query parameters.

Please will someone take a look and tell me where I am going wrong because it is really bugging me now and writing SQL is not really my forte.

I have received some help from Access guy49 on a previous database but have been away for approx 3 weeks and I think the thread would be a bit old now, hence the new thread.

I would appreciate any help, you would save me a massive headache!!!

Cheers (database attached)

 

[Brianwarnock]

Read this thread for handling Nulls.

brian

 

[Sirkevin]

Thanks for this, however I have followed what I think is being suggested and it keeps returning all of the results not a selection based on my criteria.

I have attached what I have done below qrymain1 and searchform2.

Could someone point out where I have gone wrong, I am sure it is something simple.

Thanks

 

[Alansidman]

Take a look at this video tutorial. It offers an alternative that I think will work for you. You have most of it in your form, but the query is different.

http://www.datapigtechnologies.com/flashfiles/searchform.html

Alan