That is some really slick code, Allen!! With your code, Access was
telling
me that 'LastName' and 'FirstName' was 0. Well, I couldn’t find the
problem
so I rebuilt the report, now I am also 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, optCross, optBoth. 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, the report
doesn’t get filtered correctly because of this part. What do you think
could
be causing this, Allen?
Thanks!
Ryan--
--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.
Allen Browne said:
Look in:
- the Sorting And Grouping pane
- the Filter and Order By properties of the report
- the Control Source of controls (you probably looked there.)
If not found, examine the RecordSource of the report. If it's a query,
try
running the query itself and see if it asks for parameters. Even if it's
a
table, try opening that directly, as it could be in the table's Filter or
OrderBy properties, or the RowSource of one of its fields.
If you're still stuck, this might help you find it programmatically:
Where is a field used? - Search tables, queries, forms, reports
at:
http://allenbrowne.com/ser-73.html
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Reply to group, rather than allenbrowne at mvps dot org.
When I open a report I get prompted for two Parameters, FirstName and
LastName. I looked everywhere for these variables; not seeing anything
at
all, but there must be something in there that’s causing this behavior.
How
can I find out where those pesky parameters are?
Thanks!
Ryan--
--
Ryan---
If this information was helpful, please indicate this by clicking
''Yes''.
.
