B
Bob
Hi,
I created the following code based largely on a suggestion by Allen
Browne http://allenbrowne.com/ser-50.html
My form has 4 unbound multi-select list boxes and each one has a query
as its row source.
The code looks at each list box in turn building up a Where Condition.
It then uses this Where Condition to open a report
The report gets more input information from a query as its record
source.
This arrangement works has been working perfectly for 3 other
databases I've created.
The problem this time is Error 7769 - filter would be too long.
KB article - http://support.microsoft.com/default.aspx?scid=kb;en-us;207615
suggests this was a problem with Access 2000
as there was a 2000 character limit in each condition.
I'm using Access 2003 but in my testing the 2000 character limit still
seems to be there.
My database is concerned with Share trading. One of the list boxes
boxes on my form displays the stock exchange codes ( 3
alpha characters) for the 2200 companies listed on the Australian
Stock Exchange (3 x 2200 = 6600)
If I reduce the list of companies to 650 everything works no problem.
The KB article for Access 2000 suggests using Filter Name instead of
Where Condition but it doesnt offer any suggestions on how I might
achieve this.
Any suggestions on how I might overcome this problem
Thanks
Bob
The SQL for the report is:-
SELECT tbl_Trade.Buy_Sell, tbl_Broker.Broker_ID, tbl_Broker.Broker,
tbl_Client.Client_ID, tbl_Client.Sort_Name, tbl_Client.Entity_Type,
tbl_Trade.Date_Order_Placed, tbl_Trade.Date_Ordered_Completed,
tbl_Adviser.Adviser_ID, tbl_Adviser.Adviser_First_Name AS Adviser,
tbl_Client.[Account No], tbl_ASXListedCompanies.Stock_ID,
tbl_ASXListedCompanies.ASX_Code, tbl_ASXListedCompanies.Company_Name,
tbl_Trade.Amount_Dollars AS [Buy $], tbl_Trade.Amount_Units AS [Buy
Units], tbl_Trade.Price_Limit, tbl_Trade.Brokerage_percent, tbl_Trade.
[Brokerage_min$], tbl_Trade.Date_ContractNote_Received,
tbl_Trade.Date_Brokerage_Entered, tbl_Trade.Date_Posted_To_Visi
FROM tbl_Client INNER JOIN (tbl_Broker INNER JOIN (tbl_Adviser INNER
JOIN (tbl_ASXListedCompanies INNER JOIN tbl_Trade ON
tbl_ASXListedCompanies.Stock_ID = tbl_Trade.Stock_ID) ON
tbl_Adviser.Adviser_ID = tbl_Trade.Adviser_ID) ON tbl_Broker.Broker_ID
= tbl_Trade.Broker_ID) ON tbl_Client.Client_ID = tbl_Trade.Client_ID
WHERE (((tbl_Trade.Buy_Sell)="1"))
ORDER BY tbl_Broker.Broker;
The code that loops through the list boxes and then opens a report
is:-
Where Condition 5 is commented out.
Code Start
Public Sub PreviewReport(strTheReport As String)
On Error GoTo Err_Handler
'Purpose: Open the report filtered to the items selected in the
list box.
Dim varItem As Variant 'Selected items
Dim strWhere As String 'String to use as WhereCondition
Dim strWhere1 As String 'String to use as WhereCondition
Dim strWhere2 As String 'String to use as WhereCondition
Dim strWhere3 As String 'String to use as WhereCondition
Dim strWhere4 As String 'String to use as WhereCondition
' Dim strWhere5 As String 'String to use as WhereCondition
Dim strDescrip As String 'Description of WhereCondition
Dim strDescrip1 As String 'Description of WhereCondition
Dim strDescrip2 As String 'Description of WhereCondition
Dim strDescrip3 As String 'Description of WhereCondition
Dim strDescrip4 As String 'Description of WhereCondition
' Dim strDescrip5 As String 'Description of WhereCondition
Dim lngLen As Long 'Length of string
Dim strDelim As String 'Delimiter for this field type.
' strDelim = """" 'Delimiter appropriate to field type.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Check to see at least one selection is made in list box
If Me.lstBroker.ItemsSelected.Count = 0 Then
MsgBox "You must select at least one Broker!", vbExclamation
Me.lstBroker.SetFocus
Exit Sub
End If
'Loop through the ItemsSelected in the list box.
With Me.lstBroker
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
'Build up the filter from the bound column (hidden).
strWhere1 = strWhere1 & strDelim & .ItemData(varItem)
& strDelim & ","
'Build up the description from the text in the visible
column.
strDescrip1 = strDescrip1 & """" & .Column(1, varItem)
& """, "
End If
Next
End With
'Remove trailing comma. Add field name, IN operator, and brackets.
lngLen = Len(strWhere1) - 1
If lngLen > 0 Then
strWhere1 = "[Broker_ID] IN (" & Left$(strWhere1, lngLen) &
")"
lngLen = Len(strDescrip1) - 2
If lngLen > 0 Then
strDescrip1 = "BROKER: " & Left$(strDescrip1, lngLen)
End If
End If
'
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'
' 'For another listbox run through the loop again
'
'Check to see at least one selection is made in list box
If Me.lstAdviser.ItemsSelected.Count = 0 Then
MsgBox "You must select at least one Adviser!", vbExclamation
Me.lstAdviser.SetFocus
Exit Sub
End If
'Loop through the ItemsSelected in the list box.
With Me.lstAdviser
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
'Build up the filter from the bound column (hidden).
strWhere2 = strWhere2 & strDelim & .ItemData(varItem)
& strDelim & ","
'Build up the description from the text in the visible
column.
strDescrip2 = strDescrip2 & """" & .Column(3, varItem)
& """, "
End If
Next
End With
'Remove trailing comma. Add field name, IN operator, and brackets.
lngLen = Len(strWhere2) - 1
If lngLen > 0 Then
strWhere2 = "[Adviser_ID] IN (" & Left$(strWhere2, lngLen) &
")"
lngLen = Len(strDescrip2) - 2
If lngLen > 0 Then
strDescrip2 = "ADVISER: " & Left$(strDescrip2, lngLen)
End If
End If
'
'
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'
'For another listbox run through the loop again
'Check to see at least one selection is made in list box
If Me.lstASX.ItemsSelected.Count = 0 Then
MsgBox "You must select at least one ASX code!", vbExclamation
Me.lstASX.SetFocus
Exit Sub
End If
'Loop through the ItemsSelected in the list box.
With Me.lstASX
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
'Build up the filter from the bound column (hidden).
strWhere3 = strWhere3 & strDelim & .ItemData(varItem)
& strDelim & ","
'Build up the description from the text in the visible
column.
strDescrip3 = strDescrip3 & """" & .Column(1, varItem)
& """, "
End If
Next
End With
'Remove trailing comma. Add field name, IN operator, and brackets.
lngLen = Len(strWhere3) - 1
If lngLen > 0 Then
strWhere3 = "[Stock_ID] IN (" & Left$(strWhere3, lngLen) & ")"
lngLen = Len(strDescrip3) - 2
If lngLen > 0 Then
strDescrip3 = "ASX_Code: " & Left$(strDescrip3, lngLen)
End If
End If
''
''
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''
'' 'For another listbox run through the loop again
''
'Check to see at least one selection is made in list box
If Me.lstClient.ItemsSelected.Count = 0 Then
MsgBox "You must select at least one Client!", vbExclamation
Me.lstClient.SetFocus
Exit Sub
End If
'Loop through the ItemsSelected in the list box.
With Me.lstClient
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
'Build up the filter from the bound column (hidden).
strWhere4 = strWhere4 & strDelim & .ItemData(varItem)
& strDelim & ","
'Build up the description from the text in the visible
column.
strDescrip4 = strDescrip4 & """" & .Column(1, varItem)
& """, "
End If
Next
End With
'Remove trailing comma. Add field name, IN operator, and brackets.
lngLen = Len(strWhere4) - 1
If lngLen > 0 Then
strWhere4 = "[Client_ID] IN (" & Left$(strWhere4, lngLen) &
")"
lngLen = Len(strDescrip4) - 2
If lngLen > 0 Then
strDescrip4 = "CLIENT: " & Left$(strDescrip4, lngLen)
End If
End If
''
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''
'' 'For another listbox run through the loop again
''
'' 'Check to see at least one selection is made in list box
'' If Me.lstDebtored.ItemsSelected.Count = 0 Then
'' MsgBox "You must choose whether Debtored or not or click 'Select
All' for both!", vbExclamation
'' Me.lstDebtored.SetFocus
'' Exit Sub
'' End If
'' 'Loop through the ItemsSelected in the list box.
'' With Me.lstDebtored
'' For Each varItem In .ItemsSelected
'' If Not IsNull(varItem) Then
'' 'Build up the filter from the bound column
'' strWhere5 = strWhere5 & strDelim5
& .ItemData(varItem) & strDelim & ","
'' 'Build up the description from the text in the
visible column.
'' strDescrip5 = strDescrip5 & """" & .Column(0,
varItem) & """, "
''
'' End If
'' Next
'' End With
''
''
'' 'Remove trailing comma. Add field name, IN operator, and
brackets.
'' lngLen = Len(strWhere5) - 1
'' If lngLen > 0 Then
''
'' strWhere5 = "[Debtored] IN (" & Left$(strWhere5, lngLen) &
")"
'' lngLen = Len(strDescrip5) - 2
''
'' If lngLen > 0 Then
'' strDescrip5 = "DEBTORED: " & Left$(strDescrip5, lngLen)
'' End If
'' End If
''
'
'
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'
'Report will not filter if open, so close it.
If CurrentProject.AllReports(strTheReport).IsLoaded Then
DoCmd.Close acReport, strTheReport
End If
strWhere = "(" & strWhere1 & ") AND (" & strWhere2 & ") AND (" &
strWhere3 & ") AND (" & strWhere4 & ")" 'AND (" & strWhere5 & ")"
strDescrip = "(" & strDescrip1 & ") AND " & vbCrLf & "(" &
strDescrip2 & ") AND " & vbCrLf & "(" & strDescrip3 & ") AND " &
vbCrLf & "(" & strDescrip4 & ")" 'AND " & vbCrLf & "(" & strDescrip5 &
")"
DoCmd.OpenReport strTheReport, acViewPreview,
WhereCondition:=strWhere, OpenArgs:=strDescrip
Exit_Handler:
Exit Sub
Err_Handler:
If Err.Number <> 2501 Then 'Ignore "Report cancelled" error.
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"cmdPreview_Click"
End If
Resume Exit_Handler
End Sub
Code End
I created the following code based largely on a suggestion by Allen
Browne http://allenbrowne.com/ser-50.html
My form has 4 unbound multi-select list boxes and each one has a query
as its row source.
The code looks at each list box in turn building up a Where Condition.
It then uses this Where Condition to open a report
The report gets more input information from a query as its record
source.
This arrangement works has been working perfectly for 3 other
databases I've created.
The problem this time is Error 7769 - filter would be too long.
KB article - http://support.microsoft.com/default.aspx?scid=kb;en-us;207615
suggests this was a problem with Access 2000
as there was a 2000 character limit in each condition.
I'm using Access 2003 but in my testing the 2000 character limit still
seems to be there.
My database is concerned with Share trading. One of the list boxes
boxes on my form displays the stock exchange codes ( 3
alpha characters) for the 2200 companies listed on the Australian
Stock Exchange (3 x 2200 = 6600)
If I reduce the list of companies to 650 everything works no problem.
The KB article for Access 2000 suggests using Filter Name instead of
Where Condition but it doesnt offer any suggestions on how I might
achieve this.
Any suggestions on how I might overcome this problem
Thanks
Bob
The SQL for the report is:-
SELECT tbl_Trade.Buy_Sell, tbl_Broker.Broker_ID, tbl_Broker.Broker,
tbl_Client.Client_ID, tbl_Client.Sort_Name, tbl_Client.Entity_Type,
tbl_Trade.Date_Order_Placed, tbl_Trade.Date_Ordered_Completed,
tbl_Adviser.Adviser_ID, tbl_Adviser.Adviser_First_Name AS Adviser,
tbl_Client.[Account No], tbl_ASXListedCompanies.Stock_ID,
tbl_ASXListedCompanies.ASX_Code, tbl_ASXListedCompanies.Company_Name,
tbl_Trade.Amount_Dollars AS [Buy $], tbl_Trade.Amount_Units AS [Buy
Units], tbl_Trade.Price_Limit, tbl_Trade.Brokerage_percent, tbl_Trade.
[Brokerage_min$], tbl_Trade.Date_ContractNote_Received,
tbl_Trade.Date_Brokerage_Entered, tbl_Trade.Date_Posted_To_Visi
FROM tbl_Client INNER JOIN (tbl_Broker INNER JOIN (tbl_Adviser INNER
JOIN (tbl_ASXListedCompanies INNER JOIN tbl_Trade ON
tbl_ASXListedCompanies.Stock_ID = tbl_Trade.Stock_ID) ON
tbl_Adviser.Adviser_ID = tbl_Trade.Adviser_ID) ON tbl_Broker.Broker_ID
= tbl_Trade.Broker_ID) ON tbl_Client.Client_ID = tbl_Trade.Client_ID
WHERE (((tbl_Trade.Buy_Sell)="1"))
ORDER BY tbl_Broker.Broker;
The code that loops through the list boxes and then opens a report
is:-
Where Condition 5 is commented out.
Code Start
Public Sub PreviewReport(strTheReport As String)
On Error GoTo Err_Handler
'Purpose: Open the report filtered to the items selected in the
list box.
Dim varItem As Variant 'Selected items
Dim strWhere As String 'String to use as WhereCondition
Dim strWhere1 As String 'String to use as WhereCondition
Dim strWhere2 As String 'String to use as WhereCondition
Dim strWhere3 As String 'String to use as WhereCondition
Dim strWhere4 As String 'String to use as WhereCondition
' Dim strWhere5 As String 'String to use as WhereCondition
Dim strDescrip As String 'Description of WhereCondition
Dim strDescrip1 As String 'Description of WhereCondition
Dim strDescrip2 As String 'Description of WhereCondition
Dim strDescrip3 As String 'Description of WhereCondition
Dim strDescrip4 As String 'Description of WhereCondition
' Dim strDescrip5 As String 'Description of WhereCondition
Dim lngLen As Long 'Length of string
Dim strDelim As String 'Delimiter for this field type.
' strDelim = """" 'Delimiter appropriate to field type.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Check to see at least one selection is made in list box
If Me.lstBroker.ItemsSelected.Count = 0 Then
MsgBox "You must select at least one Broker!", vbExclamation
Me.lstBroker.SetFocus
Exit Sub
End If
'Loop through the ItemsSelected in the list box.
With Me.lstBroker
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
'Build up the filter from the bound column (hidden).
strWhere1 = strWhere1 & strDelim & .ItemData(varItem)
& strDelim & ","
'Build up the description from the text in the visible
column.
strDescrip1 = strDescrip1 & """" & .Column(1, varItem)
& """, "
End If
Next
End With
'Remove trailing comma. Add field name, IN operator, and brackets.
lngLen = Len(strWhere1) - 1
If lngLen > 0 Then
strWhere1 = "[Broker_ID] IN (" & Left$(strWhere1, lngLen) &
")"
lngLen = Len(strDescrip1) - 2
If lngLen > 0 Then
strDescrip1 = "BROKER: " & Left$(strDescrip1, lngLen)
End If
End If
'
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'
' 'For another listbox run through the loop again
'
'Check to see at least one selection is made in list box
If Me.lstAdviser.ItemsSelected.Count = 0 Then
MsgBox "You must select at least one Adviser!", vbExclamation
Me.lstAdviser.SetFocus
Exit Sub
End If
'Loop through the ItemsSelected in the list box.
With Me.lstAdviser
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
'Build up the filter from the bound column (hidden).
strWhere2 = strWhere2 & strDelim & .ItemData(varItem)
& strDelim & ","
'Build up the description from the text in the visible
column.
strDescrip2 = strDescrip2 & """" & .Column(3, varItem)
& """, "
End If
Next
End With
'Remove trailing comma. Add field name, IN operator, and brackets.
lngLen = Len(strWhere2) - 1
If lngLen > 0 Then
strWhere2 = "[Adviser_ID] IN (" & Left$(strWhere2, lngLen) &
")"
lngLen = Len(strDescrip2) - 2
If lngLen > 0 Then
strDescrip2 = "ADVISER: " & Left$(strDescrip2, lngLen)
End If
End If
'
'
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'
'For another listbox run through the loop again
'Check to see at least one selection is made in list box
If Me.lstASX.ItemsSelected.Count = 0 Then
MsgBox "You must select at least one ASX code!", vbExclamation
Me.lstASX.SetFocus
Exit Sub
End If
'Loop through the ItemsSelected in the list box.
With Me.lstASX
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
'Build up the filter from the bound column (hidden).
strWhere3 = strWhere3 & strDelim & .ItemData(varItem)
& strDelim & ","
'Build up the description from the text in the visible
column.
strDescrip3 = strDescrip3 & """" & .Column(1, varItem)
& """, "
End If
Next
End With
'Remove trailing comma. Add field name, IN operator, and brackets.
lngLen = Len(strWhere3) - 1
If lngLen > 0 Then
strWhere3 = "[Stock_ID] IN (" & Left$(strWhere3, lngLen) & ")"
lngLen = Len(strDescrip3) - 2
If lngLen > 0 Then
strDescrip3 = "ASX_Code: " & Left$(strDescrip3, lngLen)
End If
End If
''
''
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''
'' 'For another listbox run through the loop again
''
'Check to see at least one selection is made in list box
If Me.lstClient.ItemsSelected.Count = 0 Then
MsgBox "You must select at least one Client!", vbExclamation
Me.lstClient.SetFocus
Exit Sub
End If
'Loop through the ItemsSelected in the list box.
With Me.lstClient
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
'Build up the filter from the bound column (hidden).
strWhere4 = strWhere4 & strDelim & .ItemData(varItem)
& strDelim & ","
'Build up the description from the text in the visible
column.
strDescrip4 = strDescrip4 & """" & .Column(1, varItem)
& """, "
End If
Next
End With
'Remove trailing comma. Add field name, IN operator, and brackets.
lngLen = Len(strWhere4) - 1
If lngLen > 0 Then
strWhere4 = "[Client_ID] IN (" & Left$(strWhere4, lngLen) &
")"
lngLen = Len(strDescrip4) - 2
If lngLen > 0 Then
strDescrip4 = "CLIENT: " & Left$(strDescrip4, lngLen)
End If
End If
''
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''
'' 'For another listbox run through the loop again
''
'' 'Check to see at least one selection is made in list box
'' If Me.lstDebtored.ItemsSelected.Count = 0 Then
'' MsgBox "You must choose whether Debtored or not or click 'Select
All' for both!", vbExclamation
'' Me.lstDebtored.SetFocus
'' Exit Sub
'' End If
'' 'Loop through the ItemsSelected in the list box.
'' With Me.lstDebtored
'' For Each varItem In .ItemsSelected
'' If Not IsNull(varItem) Then
'' 'Build up the filter from the bound column
'' strWhere5 = strWhere5 & strDelim5
& .ItemData(varItem) & strDelim & ","
'' 'Build up the description from the text in the
visible column.
'' strDescrip5 = strDescrip5 & """" & .Column(0,
varItem) & """, "
''
'' End If
'' Next
'' End With
''
''
'' 'Remove trailing comma. Add field name, IN operator, and
brackets.
'' lngLen = Len(strWhere5) - 1
'' If lngLen > 0 Then
''
'' strWhere5 = "[Debtored] IN (" & Left$(strWhere5, lngLen) &
")"
'' lngLen = Len(strDescrip5) - 2
''
'' If lngLen > 0 Then
'' strDescrip5 = "DEBTORED: " & Left$(strDescrip5, lngLen)
'' End If
'' End If
''
'
'
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'
'Report will not filter if open, so close it.
If CurrentProject.AllReports(strTheReport).IsLoaded Then
DoCmd.Close acReport, strTheReport
End If
strWhere = "(" & strWhere1 & ") AND (" & strWhere2 & ") AND (" &
strWhere3 & ") AND (" & strWhere4 & ")" 'AND (" & strWhere5 & ")"
strDescrip = "(" & strDescrip1 & ") AND " & vbCrLf & "(" &
strDescrip2 & ") AND " & vbCrLf & "(" & strDescrip3 & ") AND " &
vbCrLf & "(" & strDescrip4 & ")" 'AND " & vbCrLf & "(" & strDescrip5 &
")"
DoCmd.OpenReport strTheReport, acViewPreview,
WhereCondition:=strWhere, OpenArgs:=strDescrip
Exit_Handler:
Exit Sub
Err_Handler:
If Err.Number <> 2501 Then 'Ignore "Report cancelled" error.
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"cmdPreview_Click"
End If
Resume Exit_Handler
End Sub
Code End
