I wouldn't put the criteria on the query. I would put it in the event which opens the form (like the click event of a button), and you can use it like this (which will just ignore it if blank):
Code:
Dim strWhere As String
' For Numeric Datatypes
If Len(Me.Control1NameHere & vbNullString) > 0 Then
strWhere = "[Field1NameHere] = " & Me.Control1NameHere & " AND " ' this assumes a numeric datatype otherwise Chr(34) is needed to add double-quote)
End If
' For text datatypes
If Len(Me.Control2NameHere & vbNullString) > 0 Then
strWhere = strWhere & "[Field2NameHere] = " & Chr(34) & Me.Control2NameHere & Chr(34) & " AND " ' this example shows that Field2NameHere is a text datatype so we added the Chr(34) code
' For date range
If Len(Me.Control3NameHere & vbNullString) > 0 And Len(Me.Control4NameHere & vbNullString) > 0 Then
strWhere = strWhere & "[DateField] Between #" & Format(Me.Control3NameHere, "mm\/dd\/yyyy") & "# AND #" & Format(Me.Control4NameHere, "mm\/dd\/yyyy") & " AND " ' Dates use # for delimiters and the format is set to make sure regional settings don't affect the SQL
End If
' If the start date is left blank
If Len(Me.Control3NameHere & vbNullString) = 0 And Len(Me.Control4NameHere & vbNullString) > 0 Then
strWhere = strWhere & "[DateField] <= #" & Format(Me.Control4NameHere, "mm\/dd\/yyyy") & " AND "
End If
' If the ending date is left blank
If Len(Me.Control3NameHere & vbNullString) > 0 And Len(Me.Control4NameHere & vbNullString) = 0 Then
strWhere = strWhere & "[DateField] >= #" & Format(Me.Control3NameHere, "mm\/dd\/yyyy") & " AND "
End If
' we remove the ending AND
If strWhere <> vbNullString Then
strWhere = Left(strWhere, Len(strWhere) - 5)
End If
' if filtering the form this code is on then
Me.Filter = strWhere
Me.FilterOn = True
' if filtering a different form by opening:
' DoCmd.OpenForm "FormNameHere", WhereCondition:=strWhere