Multiple-criteria search on form, to open form

[Mac]

I know that there are queries that can do this, but I want to try to get this
functionality on a form looking like this. After clicking 'Search', a table
should return the filtered information. Please let me know if this is
possible, and HOW to do it... preferably step by step? Thank you in advance.
If all fields are empty, should return ALL records in table, correct?

_______________________________
|Enter search criteria below |
| ___________ |
|Name | John | |
| |__________| |
| ___________ |
|Amount $ | $50 | |
| |__________| |
| ___________ |
|Date (pick)| | |
| -On | | |
| -Between |__________| |
| ______________ |
| |_S_e_a_r_c_h_| <--button |
|______________________________|

 

[Mac]

Sorry, not to 'open form' to open 'filtered table' or 'query' ??

 

[Daryl S]

Mac -

They way to do this is to build your filter when the Search button is
clicked. Then open the form to show the recrods using the criteria. Here is
the shell of the code for your button_click event. You will need to change
all names to match your form, control, and fieldnames.

Dim strCriteria as String

strCriteria = ""

If len(Me.Name) > 0 Then
strCriteria = "WHERE [fldName] = '" & Me.Name & "'"
End If

If nz(Me.Amount,0) <> 0 Then
If strCriteria = "" Then
strCriteria = "WHERE [fldAmount] = " & Me.Amount
Else
strCriteria = strCriteria & " AND [fldAmount] = " & Me.Amount
End If
End If

If (nz(Me.Date1,0) <> 0) AND (Me.optDate = 1) Then 'On date chosen
If strCriteria = "" Then
strCriteria = "WHERE [fldDate] = #" & Me.Date1 & "#"
Else
strCriteria = strCriteria & " AND [fldDate] = #" & Me.Date1 & "#"
End If
End If

If (nz(Me.Date1,0) <> 0) AND (Me.optDate = 2) Then 'Between Dates chosen
If strCriteria = "" Then
strCriteria = "[fldDate] >= #" & Me.Date1 & "#"
Else
strCriteria = strCriteria & " AND [fldDate] >= #" & Me.Date1 & "#"
End If

If (nz(Me.Date2,0) <> 0) Then
strCriteria = strCriteria & " AND [fldDate] <= #" & Me.Date2 & "#"
End If
End If

' Now open the form to show the records, and pass in this criteria:

DoCmd.OpenForm "FormName", acNormal, , strCriteria