filter on multiple fields on a form

[rrm]

How do you filter on 2 or more fields on a form? I have a form that uses
unbound combo box to select a stock number and a pair of unbound text boxes
that get a date range to further filter the selected stock number records.
That is for a given stock number show me the records that fall within this
date range.

I used the after update event for each, combo box and text box, but it will
either do the stock or date range, but not together. ( I tested each by
commenting out the other event code.)

Here is the code I use on the after update events on the 2 inputs. I think
I need some sort of "and" clause that ties the 2 fields to be filtered
together in the same filter statement but can't figure out where to put it.


Private Sub cbofindrecwhobotit_AfterUpdate()
' Find the record(s) that matches the control.
DoCmd.ApplyFilter , "Prod_Code = '" &
DblApp(Me.cbofindrecwhobotit.Value) & "'"
Me.FilterOn = True
End Sub

Private Sub txtwhobotenddat_AfterUpdate()
If txtwhobotstartdat.Value = "" Then
Me.Filter = "FULFILL_DT <= date()"
Else
Me.Filter = "FULFILL_DT between " & _
"(#" & Me.txtwhobotstartdat.Value & "#) " & _
"AND (#" & Me.txtwhobotenddat.Value & "#)"
End If
Me.FilterOn = True

Thank you for the help.

 

[John Vinson]

How do you filter on 2 or more fields on a form?

The Filter property is simply a valid SQL WHERE clause (without the
word WHERE). Try creating a Query which returns the desired values,
using the query grid, and look at its SQL view.

Private Sub cbofindrecwhobotit_AfterUpdate()
' Find the record(s) that matches the control.
DoCmd.ApplyFilter , "Prod_Code = '" &
DblApp(Me.cbofindrecwhobotit.Value) & "'"
Me.FilterOn = True
End Sub

Private Sub txtwhobotenddat_AfterUpdate()
If txtwhobotstartdat.Value = "" Then
Me.Filter = "FULFILL_DT <= date()"
Else
Me.Filter = "FULFILL_DT between " & _
"(#" & Me.txtwhobotstartdat.Value & "#) " & _
"AND (#" & Me.txtwhobotenddat.Value & "#)"
End If
Me.FilterOn = True

I'd suggest simply appending each new clause to the existing Filter
string; check for an empty string so you don't start with an AND:

Private Sub cbofindrecwhobotit_AfterUpdate()
' Find the record(s) that matches the control.

If Me.Filter & "" <> "" Then
Me.Filter = Me.Filter & " AND"
End If
Me.Filter = Me.Filter & " Prod_Code = '" & _
DblApp(Me.cbofindrecwhobotit.Value) & "'"
Me.FilterOn = True
End Sub

and similarly for the other afterupdate event.

I'm not familiar with DblApp; I'd just append Me.cboFindRecWhoBotIt
directly, but if it works for you...

John W. Vinson[MVP]

 

[Guest]

How do you filter on 2 or more fields on a form? I have a form that uses
unbound combo box to select a stock number and a pair of unbound text boxes
that get a date range to further filter the selected stock number records.
That is for a given stock number show me the records that fall within this
date range.

I used the after update event for each, combo box and text box, but it will
either do the stock or date range, but not together. ( I tested each by
commenting out the other event code.)

Here is the code I use on the after update events on the 2 inputs. I think
I need some sort of "and" clause that ties the 2 fields to be filtered
together in the same filter statement but can't figure out where to put it.


Private Sub cbofindrecwhobotit_AfterUpdate()
' Find the record(s) that matches the control.
DoCmd.ApplyFilter , "Prod_Code = '" &
DblApp(Me.cbofindrecwhobotit.Value) & "'"
Me.FilterOn = True
End Sub

Private Sub txtwhobotenddat_AfterUpdate()
If txtwhobotstartdat.Value = "" Then
Me.Filter = "FULFILL_DT <= date()"
Else
Me.Filter = "FULFILL_DT between " & _
"(#" & Me.txtwhobotstartdat.Value & "#) " & _
"AND (#" & Me.txtwhobotenddat.Value & "#)"
End If
Me.FilterOn = True

Thank you for the help.

And here is another way:

*** untested air code *** watch for line wrap ***
'---begin code ---
Private Sub cbofindrecwhobotit_AfterUpdate()
WhoBotSetFilter
End Sub

Private Sub txtwhobotenddat_AfterUpdate()
WhoBotSetFilter
End Sub

Private Sub WhoBotSetFilter()
Dim strFilter As String
strFilter = ""

If Len(Trim(Me.cbofindrecwhobotit)) > 0 Then
strFilter = "Prod_Code = '" & DblApp(Me.cbofindrecwhobotit.Value) &
"' AND "
End If

If Me.txtwhobotstartdat = "" Then
strFilter = strFilter & "FULFILL_DT <= date() AND "
Else
strFilter = strFilter & "FULFILL_DT between (#" &
Me.txtwhobotstartdat.Value & "#) AND (#" & Me.txtwhobotenddat.Value & "#) AND
"
End If

strFilter = Left(strFilter, Len(strFilter) - 5)
Me.Filter = strFilter
Me.FilterOn = True

End Sub
'---end code ---

 

[rrm]

Thanks Steve & John, I'll give it a whirl. The DblApp is something that a
person who was helping me put in there, it is a ? (don't know what to call
it) that takes the place of or is a double apostrophe.