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

[katlee]

I am using Access 2007 to design a database that will allow users to search an inventory of historical photographs. Currently I have a table with fields such as Date, ID, Description, Project, Division, Unit, Facility, Feature, Subfeature, Region, Area Office, and State. I am using a query by form method that have either text boxes or combo boxes for each of the fields. I want users to be able to enter in the fields that they want to search by and leave others blank. I want the query to disregard the parameters left blank and then display the results that match ALL the parameters entered. For example I want to be able to enter a date range, Project Name, and Region and want the results to match all three criteria not all the records within the date range plus all records associated with the project plus all records from the specified region. I think it may have something to do with the use of AND and OR. If anyone has any words of wisdom it would be greatly appreciated. This is what I have come up with so far:

SELECT [ID]
      ,[Project]
      ,[Division]
      ,[Unit]
      ,[Feature]
      ,[Subfeature]
      ,[Region]
      ,[Spec No]
      ,[State]
      ,[Date]
      ,[Description]
      ,[Photographer]
      ,[Field1]
      ,[Latitude]
      ,[Longitude]
      ,[Facility]
      ,[AreaOffice]
 
FROM   [Historical Photos]
 
WHERE  (([Project]=[Forms]![Search]![txtProject]) OR ([Forms]![Search]![txtProject] Is Null)
  AND  ([Division]=[Forms]![Search]![txtDivision]) OR ([Forms]![Search]![txtDivision] Is Null)
  AND  ([Unit]=[Forms]![Search]![txtUnit]) OR ([Forms]![Search]![txtUnit] Is Null)
  AND  ([ID]=[Forms]![Search]![txtPhotoID]) OR ([Forms]![Search]![txtPhotoID] Is Null)
  AND  ([Feature]=[Forms]![Search]![txtFeature]) OR ([Forms]![Search]![txtFeature] Is Null)
  AND  ([Subfeature]=[Forms]![Search]![txtSubfeature]) OR ([Forms]![Search]![txtSubfeature] Is Null)
  AND  ([Facility]=[Forms]![Search]![txtFacility]) OR ([Forms]![Search]![txtFacility] Is Null)
  AND  ([State]=[Forms]![Search]![cboState]) OR ([Forms]![Search]![cboState] Is Null)
  AND  ([Region]=[Forms]![Search]![cboRegion]) OR ([Forms]![Search]![cboRegion] Is Null)
  AND  ([AreaOffice]=[Forms]![Search]![cboAreaOffice]) OR ([Forms]![Search]![cboAreaOffice] Is Null)
  AND  ([Description] Like "*" & [Forms]![Search]![txtDescription] & "*") OR ([Forms]![Search]![txtDescription] Is Null)
  AND  ([Date] Between [Forms]![Search]![txtStart] AND [Forms]![Search]![txtEnd]) OR ([Forms]![Search]![txtStart] Is Null OR [Forms]![Search]![txtEnd] Is Null));

[colintis]

Try using LIKE and Nz function in your query like this, I'll only take your first parameter and the date parameter for the example:

WHERE (([Historical Photos].Project) LIKE '" & Nz([Forms]![Search]![txtProject],"*") & "')
AND (([Historical Photos].Date) >= LIKE '# & Nz([Forms]![Search]![txtStart],"*") & #')
AND (([Historical Photos].Date) <= LIKE '# & Nz([Forms]![Search]![txtEnd],"*" & #')

Take notice on the quotes, the [" &] and [& "] is used for strings. The date I would try with this method as I haven't tried using the BETWEEN function with LIKE. And also the brackets, make sure they are closed to the correct ones.

Reference for Nz function:
http://www.techonthenet.com/access/f...dvanced/nz.php
Finally, quote the codes to the CODE TAG.

[katlee]

I used what you gave me and this error message "Syntax error (missing operator) in query expression" came up and it highlighted LIKE.

[katlee]

Also I tried

WHERE ((([Historical Photos].Project) Like '" & Nz([Forms]![Search]![txtProject],"*") & "') AND (([Historical Photos].Division) Like '" & Nz([Forms]![Search]![txtDivision],"*") & "'));

as a test because there was no syntax error and the query returned zero results when entering just one parameter and zero results when entering both parameters.

[colintis]

@katlee
Can you try removing the --> ,"*" <-- in the Nz function? the "*" may be identified as a value instead of a null.

Or in a more complicate method is to create a filter condition statement in VBA. using something like this with DAO:

Dim firstCond As Boolean = False
Dim strSQL As String
strSQL = "SELECT [blah blah blah] " & _
         "FROM [Historical Photos] " & _
         "WHERE "
If Not IsNull([Forms]![Search]![txtProject]) Then
    strSQL = strSQL & "[Historical Photos].Project = [Forms]![Search]![txtProject"
End If
 

[katlee]

I tried removing "*" and the query still didn't return any results.

I thought there may be a way to do it using VBA but I have never done anything with VBA and am a little intimidated. Would I write this code in the "On Click" event of a button on the form where the users enter the parameters?

Right now I have a form thats record source is the query and am using a macro to get it to pop up and display the results. Is there a way to get this same effect using VBA?

Also, would I use what I had originally in the WHERE or use your suggestion (Like Nz)...

strSQL = "SELECT [blah blah blah] " & _ 
         "FROM [Historical Photos] " & _ 
         "WHERE " (what goes here?)

Thank you so much for your time!!!!

[colintis]

@katlee
Yes you can do that on_click event in VBA, if you want more basic reference on how to program VBA, you can click THIS LINK.

The method of working out your filter criterias have 2 methods, one is by ADO, and one is by DAO. Both works the same to connect a table, what different is the way to store the data into the table. The one I wrote above was using a DAO method, when there's a true to one of the IF criteria, it will append the filter into the SQL WHERE clause.

For the click event in VBA code with your requirements, it would be roughly in these steps to proceed.
=======================================

1. Declare the variable to store strings, connections, etc
2. Connect to the database table
3. run through the if-else statements to append addition criterias into the SQL string
4. Start executing the SQL
5. when the things are complete, use MsgBox to create pop-up message.

[NeoPa]

Colintis is broadly leading you along the right lines here, but it will always be complicated working with such a full set of SQL. I would suggest simplifying the problem, until the fundamental points are understood, then maybe expanding that new understanding to rebuild the full query. Let's start then with :

SELECT [ID]
      ,[Project]
      ,[Date]
      ,[Description]
 
FROM   [Historical Photos]
 
WHERE  (([ID]=[Forms]![Search]![txtPhotoID]) OR ([Forms]![Search]![txtPhotoID] Is Null)
  AND  ([Project]=[Forms]![Search]![txtProject]) OR ([Forms]![Search]![txtProject] Is Null)
  AND  ([Date] Between [Forms]![Search]![txtStart] And [Forms]![Search]![txtEnd]) OR ([Forms]![Search]![txtStart] Is Null OR [Forms]![Search]![txtEnd] Is Null))
  AND  ([Description] Like '*' & [Forms]![Search]![txtDescription] & '*') OR ([Forms]![Search]![txtDescription] Is Null);

The first point to note is that the following two are not equivalent (with reference to your WHERE clause) :

    ((A=1) OR (A Is Null)
AND (B=1) OR (B Is Null)
AND (C=1) OR (C Is Null))

    (((A=1) OR (A Is Null))
AND ((B=1) OR (B Is Null))
AND ((C=1) OR (C Is Null)))

In the latter the matching sets of comparisons are linked together by parentheses. This would be important if we wanted to proceed in this way, and would certainly explain why your SQL was giving unexpected results (With reference to your question about ANDs and ORs).

Borrowing from Colintis, we can actually be a little more clever than that, and do a single comparison that handles the situation where the form's control is unset :

SELECT [ID]
      ,[Project]
      ,[Date]
      ,[Description]
 
FROM   [Historical Photos]
 
WHERE  (([ID] Like Nz([Forms]![Search]![txtPhotoID],'*'))
  AND  ([Project] Like Nz([Forms]![Search]![txtProject],'*'))
  AND  ([Date] Between Nz([Forms]![Search]![txtStart],#1/1/1900#)
                   And Nz([Forms]![Search]![txtEnd],#31/12/9999#))
  AND  ([Description] Like '*' & Nz([Forms]![Search]![txtDescription],'*') & '*'))

Does this make things a little clearer?

[katlee]

The query seems to be working how it is supposed to! Hopefully I can apply this method to include all the fields.. Thanks to colintis for your patience and Neopa for breaking it down into simpler terms for such a beginner! What a valuable resource this forum is :)

[NeoPa]

You're very welcome Katlee :)

[katlee]

Spoke a little too soon.. I have three combo boxes (cboState, cboAreaOffice, cboRegion) that have queries of my master table as their source..

AND ([Region] Like Nz ([Forms]![Search]![cboRegion],"*") AND ([AreaOffice] Like Nz ([Forms]![Search]![cboAreaOffice],"*")

... is what I am using now and I get the expected results when selected one state but not the other. Do I need to do something different?

[NeoPa]

Katlee, Your SQL doesn't include anything for State, and I have no information as to what States are one or the other.

This leaves me somewhat short of information :D

[katlee]

Sorry

AND ([Region] Like Nz ([Forms]![Search]![cboRegion],"*") 
AND ([AreaOffice] Like Nz ([Forms]![Search]![cboAreaOffice],"*") 
AND ([State] Like Nz ([Forms]![Search]![cboState],"*")

To be more specific, I have a dummy set of records right now that I am using to see if the query will work. Currently, records have either "CA" or "AZ" for the state (when the database is in use records will be from all over the US). Using this SQL when choosing "AZ" the query worked fine, but "CA" did not return any records...

[NeoPa]

No worries Katlee.

Now you've formulated the SQL readably though, the answer (problem) jumps out.

The closing parenthesis on each line deals only with the Nz() function call. A further closing parenthesis is required to close each sub-statement.

BTW It's not wrong, but possibly unusual, to leave a space after Nz and before the open parenthesis. That's just display though. SQL will handle it either way.

PS. I just checked too, and my suggestion was what led you astray. I didn't include the second closing parenthesis on any of the lines so I will fix that now. It may be fixed by the time you read this, but it certainly was there when you read it. Just so you know (but I don't want to leave misinformation in my post).

[katlee]

Ok, makes sense!! My SQL is currently

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

... but something strange and perhaps unrelated to the SQL is happening. I have a 8 records with the "CA" in the State field, "Mid-Pacific" in the Region field, and "South-Central California" for the AreaOffice field, but when I select these choices from the combo boxes the query only returns 3 records... any suggestions?

Edit:
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]

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 :-)