If I read that correctly, Orange, it uses OR not AND. Is that right?
Here is a piece of code that builds the SQL based on AND.
(1) all fields must have its format set - some type of "Date", some type of "Number"
(2) there has to be a way to identify the fields - I have "txt" in front of the fields I was using to search
(3) each field's control source must have the correct name - use this query as the record source for the form (without the criteria)
Code:
Private Sub Cmd_Search_Click()
Dim strSQL As String, ctl As Control
strSQL = "SELECT * From Table1 WHERE (1=1)"
For Each ctl In Me.Controls
If Left(ctl.Name, 3) = "txt" Then
If ctl.Format Like "*Date*" Then
If Not IsNull(Me(ctl.Name)) Then strSQL = strSQL & " And ([" & ctl.ControlSource & "] =#" & Me(ctl.Name) & "#)"
ElseIf ctl.Format Like "*Number*" Then
If Not IsNull(Me(ctl.Name)) Then strSQL = strSQL & " And ([" & ctl.ControlSource & "] =" & Me(ctl.Name) & ")"
Else
If Not IsNull(Me(ctl.Name)) Then strSQL = strSQL & " And (([" & ctl.ControlSource & "] Is Null OR [" & ctl.ControlSource & "] Like '*" & Me(ctl.Name) & "*'))"
End If
End If
Next
Debug.Print strSQL
Set ctl = Nothing
End Sub