Simple question - Combo Box Filter

  • Thread starter Thread starter Harley
  • Start date Start date
H

Harley

Okay, this SHOULD be a pretty simple question.

I have an inquiry form where a user can select a primary
criteria (text control) and secondary criteria (combo
box) for a results form. Of course, the results form is
based on a query that uses the criteria from the inquiry
form. The secondary criteria is meant to apply a second
filter to records that match the primary criteria.

I'm trying to get the query to return ALL records that
match the primary criteria, if the secondary criteria
field is left blank.

Currently, however, if the secondary criteria field is
left blank, the query returns no records because none of
the records have null values for this field.

Can someone help me out with how to set up my combo
box/query so that it returns all records instead of none?
 
Hi Harley,

A small bit of VBA code can fix you right up. Example:

--- Start Code ---
Private Sub cmdSearch_Click()

Dim strFilter As String

strFilter = "[Field1] = '" & TextBox.Value & "'"
If Not Trim(ComboBox.Value) = "" Then
strFilter = strFilter & " AND [Field2] = '" & ComboBox.Value
End If
Forms!ResultsForm.Filter = strFilter
Forms!ResultsForm.Requery

End Sub
--- End Code ---

Hope this helps,
- Glen
 
Hi Harley,

Here are two variations on a query that demonstrate how to do this. In the
first query, Text1 must match Field1 and Text2 must match Field2 or Text2
must be blank.

SELECT
Field1, field2, field3
from
MyTable
(Field1=forms!frmMyForm!text1
And Field2 = Forms!frmMyForm!text2)
OR (Field1=forms!frmMyForm!text1
And Forms!frmMyForm!text2 is null)

In the second query, the criteria for field2 is slightly less restrictive by
using like and the wildcard character.

SELECT
Field1, field2, field3
from
MyTable
WHERE
Field1=forms!frmMyForm!text1
And Field2 like Forms!frmMyForm!text2 & "*"
 
Back
Top