combo box filter

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a database that is based on data from plays in a football game. There
is a form that I am trying to sort based off the values in a combo box. The
combo box looks up values from a table. The table is called tblcriteria and
has the criteria that meets certain situations. The form is run off a query
called qryloginformation

Following is some sample records from tblcriteria

tblcriteria
[name] [down] [distance] [yardline]
"3rd and Long" "3" ">10"
"3rd Down Long Backed up" "3" ">10" "<20"

the form frmlog has the following fields [down] [distance] [yardline] [play]
[result]

Is there an easy way to filter the information based off of this? As you can
see there are times when one or more of the criteria fields are blank.
 
If you mean that the form should be filtered based on the criteria selected,
then the form must have the same fields as what's available in the criteria
selection. Currently, tblcriteria has a field [name] that is not matched in
the form. Once you clear that up, you can apply a filter to the form using
the combo box's AfterUpdate event:

Me.Filter = "[down]=" & Me.ComboBox.Column(1) & " AND [distance]='" & Me.
ComboBox.Column(2) & "' AND [yardline]=" & Me.ComboBox.Column(3)
Me.FilterOn = True

When formulating the filter string, all text values must be enclosed in
single quotes ('). Change AND to OR depending on your desired logic. When
you want the filter turned off, use:
Me.Filter = ""
Me.FilterOn = False
I have a database that is based on data from plays in a football game. There
is a form that I am trying to sort based off the values in a combo box. The
combo box looks up values from a table. The table is called tblcriteria and
has the criteria that meets certain situations. The form is run off a query
called qryloginformation

Following is some sample records from tblcriteria

tblcriteria
[name] [down] [distance] [yardline]
"3rd and Long" "3" ">10"
"3rd Down Long Backed up" "3" ">10" "<20"

the form frmlog has the following fields [down] [distance] [yardline] [play]
[result]

Is there an easy way to filter the information based off of this? As you can
see there are times when one or more of the criteria fields are blank.
Click to expand...
 
I appreciate your help. I am getting a message enter paramater value for
[down]

kingston via AccessMonster.com said:
If you mean that the form should be filtered based on the criteria selected,
then the form must have the same fields as what's available in the criteria
selection. Currently, tblcriteria has a field [name] that is not matched in
the form. Once you clear that up, you can apply a filter to the form using
the combo box's AfterUpdate event:

Me.Filter = "[down]=" & Me.ComboBox.Column(1) & " AND [distance]='" & Me.
ComboBox.Column(2) & "' AND [yardline]=" & Me.ComboBox.Column(3)
Me.FilterOn = True

When formulating the filter string, all text values must be enclosed in
single quotes ('). Change AND to OR depending on your desired logic. When
you want the filter turned off, use:
Me.Filter = ""
Me.FilterOn = False
I have a database that is based on data from plays in a football game. There
is a form that I am trying to sort based off the values in a combo box. The
combo box looks up values from a table. The table is called tblcriteria and
has the criteria that meets certain situations. The form is run off a query
called qryloginformation

Following is some sample records from tblcriteria

tblcriteria
[name] [down] [distance] [yardline]
"3rd and Long" "3" ">10"
"3rd Down Long Backed up" "3" ">10" "<20"

the form frmlog has the following fields [down] [distance] [yardline] [play]
[result]

Is there an easy way to filter the information based off of this? As you can
see there are times when one or more of the criteria fields are blank.
Click to expand...
Click to expand...
 
Is "down" an actual control name on the form? It may be the name of the
field but the name of the control may be different. The same goes for the
other parameters.
I appreciate your help. I am getting a message enter paramater value for
[down]
If you mean that the form should be filtered based on the criteria selected,
then the form must have the same fields as what's available in the criteria
Click to expand...
[quoted text clipped - 30 lines]
Click to expand...
 
I got that part worked out. Now there are times when the value I select in
the combo box has a field that is empty Should I enter a wild card in that
field or is there a way for it to read that just that field is empty and to
not include it in the filter but to include everything else? Here is the code
that I have.

Private Sub lookuplog_AfterUpdate()
Me.Filter = "[down]=" & Me.lookuplog.Column(2) & " AND [distance]>" &
Me.lookuplog.Column(3) & " AND [distance]<" & Me.lookuplog.Column(4) & ""
Me.FilterOn = True
End Sub
 
I don't think a wildcard will work in this case. You'll just have to build
the filter string progressively by checking for IsNull(Me.lookuplog.Column(2))
for example.

I got that part worked out. Now there are times when the value I select in
the combo box has a field that is empty Should I enter a wild card in that
field or is there a way for it to read that just that field is empty and to
not include it in the filter but to include everything else? Here is the code
that I have.

Private Sub lookuplog_AfterUpdate()
Me.Filter = "[down]=" & Me.lookuplog.Column(2) & " AND [distance]>" &
Me.lookuplog.Column(3) & " AND [distance]<" & Me.lookuplog.Column(4) & ""
Me.FilterOn = True
End Sub
Click to expand...
 
I am sorry to bother you but if you could give me one example of how I would
add that I would really appreciate it. I really appreciate your time and help
greatly. I am on the cusp of finally solving a long and persistent problem.
 
Declare a string variable:

Dim strFilter as String


Check the values of columns in the combo box and build the string:

If IsNull(Me.lookuplog.Column(2))=False Then
strFilter = "[down]=" & Me.lookuplog.Column(2)
End If

If IsNull(Me.lookuplog.Column(3))=False Then
If IsNull(strFilter) Then
strFilter = "[distance]>" & Me.lookuplog.Column(3)
Else
strFilter = strFilter " AND [distance]>" & Me.lookuplog.Column(3)
End If
End If
...


Assign the string to the form filter:
Me.Filter = strFilter
...
 
Back
Top