Filter a Report with a Form

  • Thread starter Thread starter ryguy7272
  • Start date Start date
R

ryguy7272

I am struggling a bit of code that dynamically filters elements in a report.
I have a form with the following objects: lstCustomer, lstExecBroker, an
Option Group named ‘fraTrader’, with three radio buttons; optOption for
'Option', optCross for 'Cross', and optBoth for 'Stock'. Also, I have
cboSortOrder1, cboSortOrder2, and cboSortOrder3 as well as cboSortOrder1,
cboSortOrder2, and cboSortOrder3, all of which control sort order. Finally,
here’s the code:

Private Sub cmdApplyFilter_Click()
Dim varItem As Variant
Dim strCustomer As String
Dim strExecBroker As String
Dim strTrade As String
Dim strFilter As String
Dim strSortOrder As String

DoCmd.OpenReport "Options", acViewPreview

For Each varItem In Me.lstCustomer.ItemsSelected
strCustomer = strCustomer & ",'" & Me.lstCustomer.ItemData(varItem) _
& "'"
Next varItem
If Len(strCustomer) = 0 Then
strCustomer = "Like '*'"
Else
strCustomer = Right(strCustomer, Len(strCustomer) - 1)
strCustomer = "IN(" & strCustomer & ")"
End If

For Each varItem In Me.lstExecBroker.ItemsSelected
strExecBroker = strExecBroker & ",'" &
Me.lstExecBroker.ItemData(varItem) _
& "'"
Next varItem
If Len(strExecBroker) = 0 Then
strExecBroker = "Like '*'"
Else
strExecBroker = Right(strExecBroker, Len(strExecBroker) - 1)
strExecBroker = "IN(" & strExecBroker & ")"
End If

Select Case Me.fraTrader.Value
Case 1
strTrade = "=Option"
Case 2
strTrade = "=Cross"
Case 3
strTrade = "=Stock"
End Select

strFilter = "[CustName] " & strCustomer & _
" AND [ExecBroker] " & strExecBroker & _
" AND [TradeType] " & strTrade

If Me.cboSortOrder1.Value <> "Not Sorted" Then
strSortOrder = "[" & Me.cboSortOrder1.Value & "]"
If Me.cmdSortDirection1.Caption = "Descending" Then
strSortOrder = strSortOrder & " DESC"
End If
If Me.cboSortOrder2.Value <> "Not Sorted" Then
strSortOrder = strSortOrder & ",[" & Me.cboSortOrder2.Value & "]"
If Me.cmdSortDirection2.Caption = "Descending" Then
strSortOrder = strSortOrder & " DESC"
End If
If Me.cboSortOrder3.Value <> "Not Sorted" Then
strSortOrder = strSortOrder & ",[" & Me.cboSortOrder3.Value
& "]"
If Me.cmdSortDirection3.Caption = "Descending" Then
strSortOrder = strSortOrder & " DESC"
End If
End If
End If
End If

With Reports![Options]
..Filter = strFilter
..FilterOn = True
..OrderBy = strSortOrder
..OrderByOn = True
End With

End Sub

If I comment that out this section:
Select Case Me.fraTrader.Value
Case 1
strTrade = "=Option"
Case 2
strTrade = "=Cross"
Case 3
strTrade = "=Stock"
End Select

As well as this:
" AND [TradeType] " & strTrade

I can filter the elements and the report works the way I want it to. For
some reason, this is wrong:
Select Case Me.fraTrader.Value
.. . .
End Select

That converts the data to the wrong type, I suspect. Anyway, with that
section un-commented out, when I click ‘Apply Filter’ on the form, I am
prompted for a parameter ‘Option’, ‘Cross’, or ‘Stock’, whichever I choose
from the form, and then the report opens, and ALL ELEMENTS are filtered out –
nothing shows in the report. Also, the report doesn’t get filtered correctly
because of this part. How can I resolve this?

Thanks!
Ryan--
 
If TradeType is a Text field, you need to compare its value to a string:

Select Case Me.fraTrader.Value
Case 1
strTrade = "='Option'"
Case 2
strTrade = "='Cross'"
Case 3
strTrade = "='Stock'"
End Select

(in case you can't see them, I've added single quotes on either side of the
values)

If TradeType is a numeric field, you need to compare its value to a number,
not a word.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



ryguy7272 said:
I am struggling a bit of code that dynamically filters elements in a
report.
I have a form with the following objects: lstCustomer, lstExecBroker, an
Option Group named ‘fraTrader’, with three radio buttons; optOption for
'Option', optCross for 'Cross', and optBoth for 'Stock'. Also, I have
cboSortOrder1, cboSortOrder2, and cboSortOrder3 as well as cboSortOrder1,
cboSortOrder2, and cboSortOrder3, all of which control sort order.
Finally,
here’s the code:

Private Sub cmdApplyFilter_Click()
Dim varItem As Variant
Dim strCustomer As String
Dim strExecBroker As String
Dim strTrade As String
Dim strFilter As String
Dim strSortOrder As String

DoCmd.OpenReport "Options", acViewPreview

For Each varItem In Me.lstCustomer.ItemsSelected
strCustomer = strCustomer & ",'" & Me.lstCustomer.ItemData(varItem) _
& "'"
Next varItem
If Len(strCustomer) = 0 Then
strCustomer = "Like '*'"
Else
strCustomer = Right(strCustomer, Len(strCustomer) - 1)
strCustomer = "IN(" & strCustomer & ")"
End If

For Each varItem In Me.lstExecBroker.ItemsSelected
strExecBroker = strExecBroker & ",'" &
Me.lstExecBroker.ItemData(varItem) _
& "'"
Next varItem
If Len(strExecBroker) = 0 Then
strExecBroker = "Like '*'"
Else
strExecBroker = Right(strExecBroker, Len(strExecBroker) - 1)
strExecBroker = "IN(" & strExecBroker & ")"
End If

Select Case Me.fraTrader.Value
Case 1
strTrade = "=Option"
Case 2
strTrade = "=Cross"
Case 3
strTrade = "=Stock"
End Select

strFilter = "[CustName] " & strCustomer & _
" AND [ExecBroker] " & strExecBroker & _
" AND [TradeType] " & strTrade

If Me.cboSortOrder1.Value <> "Not Sorted" Then
strSortOrder = "[" & Me.cboSortOrder1.Value & "]"
If Me.cmdSortDirection1.Caption = "Descending" Then
strSortOrder = strSortOrder & " DESC"
End If
If Me.cboSortOrder2.Value <> "Not Sorted" Then
strSortOrder = strSortOrder & ",[" & Me.cboSortOrder2.Value & "]"
If Me.cmdSortDirection2.Caption = "Descending" Then
strSortOrder = strSortOrder & " DESC"
End If
If Me.cboSortOrder3.Value <> "Not Sorted" Then
strSortOrder = strSortOrder & ",[" & Me.cboSortOrder3.Value
& "]"
If Me.cmdSortDirection3.Caption = "Descending" Then
strSortOrder = strSortOrder & " DESC"
End If
End If
End If
End If

With Reports![Options]
.Filter = strFilter
.FilterOn = True
.OrderBy = strSortOrder
.OrderByOn = True
End With

End Sub

If I comment that out this section:
Select Case Me.fraTrader.Value
Case 1
strTrade = "=Option"
Case 2
strTrade = "=Cross"
Case 3
strTrade = "=Stock"
End Select

As well as this:
" AND [TradeType] " & strTrade

I can filter the elements and the report works the way I want it to. For
some reason, this is wrong:
Select Case Me.fraTrader.Value
. . .
End Select

That converts the data to the wrong type, I suspect. Anyway, with that
section un-commented out, when I click ‘Apply Filter’ on the form, I am
prompted for a parameter ‘Option’, ‘Cross’, or ‘Stock’, whichever I choose
from the form, and then the report opens, and ALL ELEMENTS are filtered
out –
nothing shows in the report. Also, the report doesn’t get filtered
correctly
because of this part. How can I resolve this?

Thanks!
Ryan--
Click to expand...
 
Thanks for the help Douglas!! That totally makes sense. As it turns out,
the code came from here:
http://www.fontstuff.com/access/acctut19.htm#dialog4

I thought I recognized it. I haven't been to that site in a couple years;
almost forgot about it. Anyway, I got it working based on the tips at that
site.

Thanks for everything!
Ryan--

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


Douglas J. Steele said:
If TradeType is a Text field, you need to compare its value to a string:

Select Case Me.fraTrader.Value
Case 1
strTrade = "='Option'"
Case 2
strTrade = "='Cross'"
Case 3
strTrade = "='Stock'"
End Select

(in case you can't see them, I've added single quotes on either side of the
values)

If TradeType is a numeric field, you need to compare its value to a number,
not a word.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



ryguy7272 said:
I am struggling a bit of code that dynamically filters elements in a
report.
I have a form with the following objects: lstCustomer, lstExecBroker, an
Option Group named ‘fraTrader’, with three radio buttons; optOption for
'Option', optCross for 'Cross', and optBoth for 'Stock'. Also, I have
cboSortOrder1, cboSortOrder2, and cboSortOrder3 as well as cboSortOrder1,
cboSortOrder2, and cboSortOrder3, all of which control sort order.
Finally,
here’s the code:

Private Sub cmdApplyFilter_Click()
Dim varItem As Variant
Dim strCustomer As String
Dim strExecBroker As String
Dim strTrade As String
Dim strFilter As String
Dim strSortOrder As String

DoCmd.OpenReport "Options", acViewPreview

For Each varItem In Me.lstCustomer.ItemsSelected
strCustomer = strCustomer & ",'" & Me.lstCustomer.ItemData(varItem) _
& "'"
Next varItem
If Len(strCustomer) = 0 Then
strCustomer = "Like '*'"
Else
strCustomer = Right(strCustomer, Len(strCustomer) - 1)
strCustomer = "IN(" & strCustomer & ")"
End If

For Each varItem In Me.lstExecBroker.ItemsSelected
strExecBroker = strExecBroker & ",'" &
Me.lstExecBroker.ItemData(varItem) _
& "'"
Next varItem
If Len(strExecBroker) = 0 Then
strExecBroker = "Like '*'"
Else
strExecBroker = Right(strExecBroker, Len(strExecBroker) - 1)
strExecBroker = "IN(" & strExecBroker & ")"
End If

Select Case Me.fraTrader.Value
Case 1
strTrade = "=Option"
Case 2
strTrade = "=Cross"
Case 3
strTrade = "=Stock"
End Select

strFilter = "[CustName] " & strCustomer & _
" AND [ExecBroker] " & strExecBroker & _
" AND [TradeType] " & strTrade

If Me.cboSortOrder1.Value <> "Not Sorted" Then
strSortOrder = "[" & Me.cboSortOrder1.Value & "]"
If Me.cmdSortDirection1.Caption = "Descending" Then
strSortOrder = strSortOrder & " DESC"
End If
If Me.cboSortOrder2.Value <> "Not Sorted" Then
strSortOrder = strSortOrder & ",[" & Me.cboSortOrder2.Value & "]"
If Me.cmdSortDirection2.Caption = "Descending" Then
strSortOrder = strSortOrder & " DESC"
End If
If Me.cboSortOrder3.Value <> "Not Sorted" Then
strSortOrder = strSortOrder & ",[" & Me.cboSortOrder3.Value
& "]"
If Me.cmdSortDirection3.Caption = "Descending" Then
strSortOrder = strSortOrder & " DESC"
End If
End If
End If
End If

With Reports![Options]
.Filter = strFilter
.FilterOn = True
.OrderBy = strSortOrder
.OrderByOn = True
End With

End Sub

If I comment that out this section:
Select Case Me.fraTrader.Value
Case 1
strTrade = "=Option"
Case 2
strTrade = "=Cross"
Case 3
strTrade = "=Stock"
End Select

As well as this:
" AND [TradeType] " & strTrade

I can filter the elements and the report works the way I want it to. For
some reason, this is wrong:
Select Case Me.fraTrader.Value
. . .
End Select

That converts the data to the wrong type, I suspect. Anyway, with that
section un-commented out, when I click ‘Apply Filter’ on the form, I am
prompted for a parameter ‘Option’, ‘Cross’, or ‘Stock’, whichever I choose
from the form, and then the report opens, and ALL ELEMENTS are filtered
out –
nothing shows in the report. Also, the report doesn’t get filtered
correctly
because of this part. How can I resolve this?

Thanks!
Ryan--
Click to expand...

.
Click to expand...
 
Back
Top