SQL string is too long...stuck!! 1

[NRK]

I have created a form that allows users to dynamically filter a report through a listbox. The listbox selections are pushed into a SQL string.

This works fine for a limited amount of selections (around 50-60 records). After that, I receive run-time error '2176' ("The setting for this property is too long&quot. My assumption is that the SQL string exceeds the limits of the Str property, which I believe is 2,048 characters.

Is there some way to increase the character length of the Str property or can I use some other data type that allows lengthy data strings?

Any help would be greatly appreciated!

 

[WildHare]

Actually, a fixed length string can be up to 65K in size. I think your problem is elsewhere. This may take a bit of SherlockHolmes-ing, but I bet you have an unmatched quote somewhere that's throwing off the parser. Check your value lists carefully and see.

"Remember, you're unique - just like everyone else"
You're invited to visit another free Access forum:

http://www.forumco.com/jmhare

or my site,

http://www.jmhare.com

 

[NRK]

Jim,
Pardon my ignorance, but I am confused on strings. I am trying to learn about VB by creating this database.

My statement was in reference to a chart I found in "Access 2000 VBA Handbook." There are three Str Data types mentioned:

Data Type Description
String (variable length): String, 0 to 2 billion characters. By default, a string variable is a variable-length string.
String (fixed length): 1 to approx. 65,400 characters.
String: Up to 2048 characters...


So, I am confused on the limitations of a Str. I will review my query output to see if I can find some problem. I am unsure what that could be as I am dynamically creating the SQL string that filters the report. So, it seems, that if there was an issue I would be getting error regardless of Str size.

I really appreciate your rapid answer and would be grateful for any further feedback.

 

[NRK]

As further information, I will post my Filter code. I think that part of the problem may be that I am assigning a byte to each row in the listbox, so that could also be a limiting factor.

This code was not my development exclusively. I would like to thank papparuff for all of his assistance.

Code:
Private Sub cmdFilter_Click()
Dim strVar As String, varItem As Variant, i As Byte

If IsNull(Me!listFilter.ListCount) Then
MsgBox "You have not selected any records to filter this report.", vbInformation
Exit Sub
Else
For i = 0 To Me.listFilter.ListCount - 1
Me.listFilter.Selected(i) = True
Next i
End If

For Each varItem In Me!listFilter.ItemsSelected
strVar = strVar & "tcID = " & "'" & Me!listFilter.Column(0, varItem) & "'" & " OR "
Next varItem

strVar = Left$(strVar, Len(strVar) - 4)

If strVar <> &quot;&quot; Then
Reports![RPT_Customer].Filter = strVar
Reports![RPT_Customer].FilterOn = True
Else
MsgBox &quot;You have not selected any records to filter this report.&quot;, vbInformation
End If

End Sub

Again, any advice would be greatly appreciated.

 

[Aivars]

Hi!

I met problem like your before. I solved it by creating temporary query for listbox. It works fine and I never met this error later.

Here's code example:

........
.........
strSQL = &quot;Select .....&quot;
strFrom = &quot;From .....&quot;
strWhere = &quot;Where .....&quot;
strGroup = &quot;Group By .....&quot;
strOrder = &quot;Order By .....&quot;
strSQL = strSQL & strFrom & strWhere & strGroup & strOrder & &quot;;&quot;
Call NewQuery(strSQL, &quot;qryTemp&quot 'Call function which creates query
Me.lstListBox.RowSource = &quot;qryTemp&quot; 'New listbox RowSource setting

Function NewQuery(strSQL As String, strQueryName As String)
'This function creates new query
'It checks for existing query with same name and removes if finds it
'
'Parameter strSQL >>> SQL text
'Parameter strQueryName >>> query name
Dim dbs As Database, myQueryDefine As QueryDef

'Return reference to current database.
Set dbs = CurrentDb

'Deleting old query with same name
On Error GoTo Err_NewQuery
dbs.QueryDefs.Delete strQueryName

Err_NewQuery:
'Creating new query.
Set myQueryDefine = dbs.CreateQueryDef(strQueryName, strSQL)
Set dbs = Nothing

End Function

Aivars

 

[NRK]

Aivars
Thanks for the code. I must admit that your example is a little over my head.

Here is the order in which this form operates:
1. User selects Category (combo box)
2. Listbox1 is populated with records that correspond to Category field found in combo box.
3. User selects desired records from Listbox1, clicks Cmd1, which sends records to Listbox2.
4. On Cmd2, Listbox2 is converted to a Str that includes necessary static data to filter report. Example: if record in Listbox2 is TC-ES001, the Str would be tcID='TC-ES001' OR - this is the string which filters the report.

So, although your code looks great I am unsure where to create the temporary Query and which listbox should be use it as its source (I believe it is Listbox2).

 

[B827]

You could use a variation on Aivars code.

Create a large text box on the form. Name this something like qryText. Take Aivars code which is coloured blue and change the last line to

Me.qryText = strSQL

this will place the sql into the textbox. It can be then copied and pasted into a query for evualation. Sometimes Access won't accept the sql if the string is too &quot;bad&quot;. In whicn case you can edit until you identify the problem.

Good hunting! Sandy

 

[Aivars]

If you would like to include several values of combobox in criteria clause.
One of ways to create record source for your report:
1) declare any string type variable (top of form module);
2) create command button for clearing of selection e.g. cmd3
3) change list boxes row sources on each update of related objects (combobox, listbox1)

Option Compare Database
Option Explicit

Dim strWhere as String

private sub Cmd3_Click()
strWhere =&quot;&quot;
Listbox1.rowsource=&quot;&quot;
Listbox2.rowsource=&quot;&quot;
end sub

private sub cboCategory_AfterUpdate() 'Category combo box
dim strSQL as string

strSQL=&quot;Select tcID, tcName From MyTable Where Category='&quot; & me.cboCategory & &quot;'&quot;
'Change first list box row source accordingly combo box selection
me.Listbox1.rowsource=strSQL
end sub

private sub Cmd1_Click()
dim strSQL as string
dim strWhere0 as string

'For multiple selection better is using instruction IN
if strWhere <>&quot;&quot; then
strWhere = strWhere & &quot;,&quot;
end if
strWhere = strWhere & &quot;'&quot; & cstr(me.Listbox1) & &quot;'&quot;

'Where clause for second list box row source
strWhere0 =&quot;Where tcID IN(&quot; & strWhere & &quot&quot;
'Compose SQL text
'Next follows sample only
strSQL = &quot;Select * From MyTable &quot; & strWhere & &quot;;&quot;

Call NewQuery(strSQL, &quot;qryTemp&quot 'Call function which creates query
Me.Listbox2.RowSource = &quot;qryTemp&quot; 'New listbox RowSource setting
Me.Listbox2=Me.Listbox1 'Just now added value

end sub
'Include new query (&quot;qryTemp&quot in record source of your report.

private sub Cmd2_Click()
'Open report
docmd.OpenReport &quot;MyReport&quot;,acViewPreview
end sub


Aivars

 

[Aivars]

Sorry, just now I found mistake. Right code is:

.............
.............
'Compose SQL text
'Next follows sample only
strSQL = &quot;Select * From MyTable &quot; & strWhere0 & &quot;;&quot;

Call NewQuery(strSQL,......
........

Aivars