Multiple parameter query; ignore blanks & have results match all parameters entered

[NeoPa]

If it finds some but not others this generally points to something different between the records. As you say nothing about any differences I have nothing to work with I'm afraid.

If you'd like to attach a copy of the database where this behaviour occurs I could look at it for you if you like.

When attaching your work please follow the following steps first :

1. Remove anything not relevant to the problem. This is not necessary in all circumstances but some databases can be very bulky and some things do not effect the actual problem at all.
2. Likewise, not entirely necessary in all cases, but consider saving your database in a version not later than 2003 as many of our experts don't use Access 2007. Largely they don't want to, but some also don't have access to it. Personally I will wait until I'm forced to before using it.
3. If the process depends on any linked tables then make local copies in your database to replace the linked tables.
4. If you've done anything in steps 1 to 3 then make sure that the problem you're experiencing is still evident in the updated version.
5. Compile the database (From the Visual Basic Editor select Debug / Compile {Project Name}).
6. Compact the database (Tools / Database Utilities / Compact and Repair Database...).
7. Compress the database into a ZIP file.
8. When posting, scroll down the page and select Manage Attachments (Pressing on that leads you to a page where you can add or remove your attachments. It also lists the maximum file sizes for each of the allowed file types.) and add this new ZIP file.

It's also a good idea to include some instructions that enable us to find the issue you'd like help with. Maybe some instructions of what to select, click on, enter etc that ensures we'll see what you see and have the same problems.

[katlee]

I realized that what the three records have in common is they have values in all the other fields being queried (none of the fields were left blank in the table)... the problem is that some records will not have values in a certain field because the information is unknown. For example, very old photographs were assigned Photo IDs and in a certain year this stopped being done. I want users to be able to search by the ID if they know it, but if they want all the records from a certain time period (without knowing and ID) they could enter that and records with or without a Photo ID would be returned (currently only the ones with an ID within that time period are being returned. I hope this makes sense..

[NeoPa]

Ah. In that case try :

WHERE ((Nz([ID],'') Like Nz([Forms]![Search]![txtPhotoID],'*'))
  AND (Nz([Project],'') Like Nz([Forms]![Search]![txtProject],'*'))
  AND (Nz([Date],#1/1/1900#) Between Nz([Forms]![Search]![txtStart],#1/1/1900#)
                                 And Nz([Forms]![Search]![txtEnd],#31/12/9999#))
  AND (Nz([Description],'') Like '*' & Nz([Forms]![Search]![txtDescription],'*') & '*'))
  AND (Nz([Division],'') Like Nz([Forms]![Search]![txtDivision], '*'))
  AND (Nz([AreaOffice],'') Like Nz([Forms]![Search]![cboAreaOffice], '*'))
  AND (Nz([Region],'') Like Nz([Forms]![Search]![cboRegion], '*'))
  AND (Nz([State],'') Like Nz([Forms]![Search]![cboState], '*'));

[katlee]

Wow works like a charm!! Many, many thanks!!!

[NeoPa]

A pleasure.

I'm glad it worked for you :)

[NBRJ]

I just signed up to say thank you to both Katlee and NeoPa.

Katlee for asking the very same question I had (I've got 13 fields in my search of various types). To NeoPa for posting the solution and examples that cover most field types and explaining how it works. That you both posted the code until it was correct was very helpful.

My monster search is working as a result! THANK you both so much :)

[NeoPa]

You're very welcome.

We like to stay conscious that our threads are even more useful to others than they are for those involved when they're filled.

Thank you for taking the trouble to post. We appreciate it :-)