Filter a Report with a Form

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--
 
D

Douglas J. Steele

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...
 
R

ryguy7272

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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Code to Dynamically Filter a Report; Not Working 2
Filter by form with beginning date and enddate, where calender we 1
How can I filter by Dates on a Form? 6
Between Dates on Combo Form 10
Delete a Query, or Empty it and Reload With New SQL? 2
Using a Form to Create a Query. Problem with VBA code... 2
Seeking Help With VBA and SQL Instant Query 8
Build Dynamic Query from Form 6

Top