Force empty query in search form when search fields are blank

Question

I have created a search form which uses the fields within it to filter the contained subform. This works fine, except when the search fields are blank all records in the query show.

I would like for users to see no records until they start to fill out the search criteria (only when search fields are blank).

Note: Not all search criteria is required, so some fields may be blank when the form is used.

Another Note: I am NOT attempting to filter out blank fields within the query.

*** EDIT: I have a form, let's call it frmA. Within this form there is a subform, let's call it sfmB. The record source for sfmB is a query we'll call qryB.

Sample query (which again, works as it should):

SELECT FieldA, FieldB, FieldC, FieldD, FieldE
FROM tblA
WHERE (FieldA Like "*" & [Forms]![frmA]![FieldA] & "*") 
  AND (FieldB Like "*" & [Forms]![frmA]![FieldB] & "*") AND ...;

Have tried:

WHERE (IIf(IsNull([Forms]![frmA]![FieldA])=True, "", FieldA 
                  Like "*" & [Forms]![frmA]![FieldA] & "*"))...

Also tried:

WHERE (IIf([Forms]![frmA]![FieldA])="", "", FieldA 
           Like "*" & [Forms]![frmA]![FieldA] & "*")...

Also tried:

WHERE (IIf([Forms]![frmA]![FieldA])=Null, "", FieldA 
           Like "*" & [Forms]![frmA]![FieldA] & "*")...

Answer 1

Try this:

WHERE ([Forms]![frmA]![FieldA] Not Is Null) AND (FieldA Like "*" & [Forms]![frmA]![FieldA] & "*")

Answer 2

If you want no records returned when all search criteria are blank then you have to explicitly and separately test for this condition in your WHERE clause. Something like:

SELECT FieldA, FieldB, FieldC, FieldD, FieldE
FROM tblA
WHERE 
  NOT ( IsNull([Forms]![frmA]![FieldA]) 
        AND IsNull([Forms]![frmA]![FieldB]) 
        AND IsNull([Forms]![frmA]![FieldC]) 
        AND IsNull([Forms]![frmA]![FieldD]) 
        AND IsNull([Forms]![frmA]![FieldE]) )
  AND (FieldA Like "*" & [Forms]![frmA]![FieldA] & "*") 
  AND (FieldB Like "*" & [Forms]![frmA]![FieldB] & "*") AND ...;

So if all the search fields on frmA are null then the condition inside the NOT (...) brackets will be TRUE, the NOT will turn this into FALSE and so your whole WHERE clause will be FALSE and no rows will be returned. If any if the search fields has a value then inside the NOT (...) will be FALSE, the NOT will turn this TRUE and then any other conditions applied by the rest of your WHERE clause will come in to play.

EDIT - I have just re-read the answer from Parfait and actually I think that is a neater solution!

Answer 3

Reason all records return is the empty form controls return any value as you are concatenating nothing with the wildcards so returns this search pattern ** in the LIKE evaluation. As shown below with empty strings or nulls, this search pattern returns all records that have any non-empty character.

SELECT FieldA, FieldB, FieldC, FieldD, FieldE
FROM tblA
WHERE (FieldA Like "*" & '' & "*") 
  AND (FieldB Like "*" & '' & "*") AND ...;    

SELECT FieldA, FieldB, FieldC, FieldD, FieldE
FROM tblA
WHERE (FieldA Like "*" & NULL & "*") 
  AND (FieldB Like "*" & NULL & "*") AND ...;

To resolve, consider concatenating a character(s) that would not show up in you columns with the NZ() replacement. Below logic now will return all records by form search values as is. However, if form values are blank, NZ() substitutes with ~ and any records with this character in column will return. Assuming no tilda ever exists in that column, no records output.

SELECT FieldA, FieldB, FieldC, FieldD, FieldE
FROM tblA
WHERE (FieldA Like "*" & NZ([Forms]![frmA]![FieldA], '~') & "*") 
  AND (FieldB Like "*" & NZ([Forms]![frmA]![FieldB], '~') AND ...;

---

Turns out for full implementation the NZ() substitution value must be replaced dynamically. Hence, consider incorporating VBA per mutually exclusive conditions:

1. When all fields are empty, NZ() should use tilda (~).

2. When at least one field is non-empty, all remaining empty fields' NZ() should use the wildcard operator, asterisk (*).

Dim i As Integer, num_search_fields As Integer
Dim strSQL As String
Dim var As Variant

i = 0
num_search_fields = 5
' LOOP THROUGH ALL SEARCH FIELDS
For Each var in Array("FieldA", "FieldB", "FieldC", "FieldD", "FieldE")
    If IsNull(Forms("frmA").Controls(var).Value) Then
         i = i + 1
    End If
Next var

' CONDITIONALLY BUILD SQL
If i = num_search_fields Then
      strSQL = "SELECT FieldA, FieldB, FieldC, FieldD, FieldE" _
                 & " FROM tblA" _
                 & " WHERE (FieldA Like '*' & NZ([Forms]![frmA]![FieldA], '~') & '*')" _
                 & " AND (FieldB Like '*' & NZ([Forms]![frmA]![FieldB], '~') & '*')" _
                 & " AND (FieldC Like '*' & NZ([Forms]![frmA]![FieldC], '~') & '*')" _
                 & " AND (FieldD Like '*' & NZ([Forms]![frmA]![FieldD], '~') & '*')" _
                 & " AND (FieldE Like '*' & NZ([Forms]![frmA]![FieldE], '~') & '*')" 
Else
      strSQL = "SELECT FieldA, FieldB, FieldC, FieldD, FieldE" _
                 & " FROM tblA" _
                 & " WHERE (FieldA Like '*' & NZ([Forms]![frmA]![FieldA], '*') & '*')" _
                 & " AND (FieldB Like '*' & NZ([Forms]![frmA]![FieldB], '*') & '*')" _
                 & " AND (FieldC Like '*' & NZ([Forms]![frmA]![FieldC], '*') & '*')" _
                 & " AND (FieldD Like '*' & NZ([Forms]![frmA]![FieldD], '*') & '*')" _
                 & " AND (FieldE Like '*' & NZ([Forms]![frmA]![FieldE], '*') & '*')" 
End If

' ASSIGN SQL STRING TO SUBFORM RECORDSOURCE
Forms!frmA!subform.Form.RecordSource = strSQL
Forms!frmA!subform.Form.Requery