Is there a limit to VBA line for filter? (1 Viewer)

[Malcolm17]

Hello,

I'm trying to filter and print a report using a button on a form, I can get it to work, however I seem to be a little bit short for my ideal filter. I can get the following to work (with 15 criteria):

DoCmd.OpenReport "rptBuyingList", acViewReport, , "STOCK_CAT = Forms!frmBackground!subfrmBuyingList!txtBLCat1 Or STOCK_CAT = Forms!frmBackground!subfrmBuyingList!txtBLCat2 Or STOCK_CAT = Forms!frmBackground!subfrmBuyingList!txtBLCat3 Or STOCK_CAT = Forms!frmBackground!subfrmBuyingList!txtBLCat4 Or STOCK_CAT = Forms!frmBackground!subfrmBuyingList!txtBLCat5 Or STOCK_CAT = Forms!frmBackground!subfrmBuyingList!txtBLCat6 Or STOCK_CAT = Forms!frmBackground!subfrmBuyingList!txtBLCat7 Or STOCK_CAT = Forms!frmBackground!subfrmBuyingList!txtBLCat8 Or STOCK_CAT = Forms!frmBackground!subfrmBuyingList!txtBLCat9 Or STOCK_CAT = Forms!frmBackground!subfrmBuyingList!txtBLCat10 Or STOCK_CAT = Forms!frmBackground!subfrmBuyingList!txtBLCat11 Or STOCK_CAT = Forms!frmBackground!subfrmBuyingList!txtBLCat12 Or STOCK_CAT = Forms!frmBackground!subfrmBuyingList!txtBLCat13 Or STOCK_CAT = Forms!frmBackground!subfrmBuyingList!txtBLCat14 Or STOCK_CAT = Forms!frmBackground!subfrmBuyingList!txtBLCat15", acHidden

However I want to add a 16th criteria and I have tried various bits, I though this would be successful but it hasn't. It might be that I use a query, but I'd prefer not to as I can use my form with various filters from VBA buttons, or maybe I just accept that 15 is all I can have?

This is what I thought would work:

DoCmd.OpenReport "rptBuyingList", acViewReport, , "STOCK_CAT = Forms!frmBackground!subfrmBuyingList!txtBLCat1 _
or STOCK_CAT = Forms!frmBackground!subfrmBuyingList!txtBLCat2 _
or STOCK_CAT = Forms!frmBackground!subfrmBuyingList!txtBLCat3 _
or STOCK_CAT = Forms!frmBackground!subfrmBuyingList!txtBLCat4 _
or STOCK_CAT = Forms!frmBackground!subfrmBuyingList!txtBLCat5 _
or STOCK_CAT = Forms!frmBackground!subfrmBuyingList!txtBLCat6 _
or STOCK_CAT = Forms!frmBackground!subfrmBuyingList!txtBLCat7 _
or STOCK_CAT = Forms!frmBackground!subfrmBuyingList!txtBLCat8 _
or STOCK_CAT = Forms!frmBackground!subfrmBuyingList!txtBLCat9 _
or STOCK_CAT = Forms!frmBackground!subfrmBuyingList!txtBLCat10 _
or STOCK_CAT = Forms!frmBackground!subfrmBuyingList!txtBLCat11 _
or STOCK_CAT = Forms!frmBackground!subfrmBuyingList!txtBLCat12 _
or STOCK_CAT = Forms!frmBackground!subfrmBuyingList!txtBLCat13 _
or STOCK_CAT = Forms!frmBackground!subfrmBuyingList!txtBLCat14 _
or STOCK_CAT = Forms!frmBackground!subfrmBuyingList!txtBLCat15 _
or STOCK_CAT = Forms!frmBackground!subfrmBuyingList!txtBLCat16", acHidden

The error message that I get returned it Compile error: Syntax error

Any advice would be appreciated.

Many thanks

Malcolm

 

[theDBguy]

Looks like that should work. If not, maybe you could try it this way:

DoCmd.OpenReport "rptBuyingList", acViewReport, , "STOCK_CAT IN(Forms!frmBackground!subfrmByingList.Form!txtBLCat1, Forms!...txtBLCat2, Forms...txtBLCat3, etc...)

Hope that makes sense...

 

[plog]

The syntax error is all those underscores.

"If you want to breakup text " _
& "into multiple lines you must put ending quotes " _
& "around your string then use the underscore " _
& "then start the new line with an ampersand"

However, the best way to achieve what you want is to just use a string variable and compile your criteria in it:

str_Criteria = "STOCK_CAT = '" & Forms!frmBackground!subfrmBuyingList!txtBLCat1
str_Criteria= str_Criteria & "OR STOCK_CAT = '" & Forms!frmBackground!subfrmBuyingList!txtBLCat2
...
str_Criteria= str_Criteria & "OR STOCK_CAT = '" & Forms!frmBackground!subfrmBuyingList!txtBLCat597

DoCmd.OpenReport "rptBuyingList", acViewReport, , str_Criteria, acHidlden

Since all your form inputs are numerically named you don't really have to have 1 line per criterion. You can just use a loop and compile your criteria string inside it.

 

[MajP]

I think most people would use a multiselect listbox to do this. I sure would not want to type into 16 textboxes. Two different versions.

 

[ebs17]

Modification of plog's hint:

str_Criteria = "STOCK_CAT IN (2, 4, 5, 88)"      ' field contents as an integer
str_Criteria = "STOCK_CAT IN ('blue', 'yellow', 'green', 'white')"      ' field contents as a string

A passed filter could look like this. You're using VBA anyway. You can put the field contents together in a loop to form such a bracketed expression.

So you could combine the selection of a list box with multiple selections, but also a number of controls in a record or unbound controls.

 

[Minty]

As others have said there are better approaches to this.
Your actual problem is that there is a limit on the number of continuation lines and I believe it's 15.

Most people won't have hit this but I have building long SQL strings which is why I use the following type of construct which avoids the issue.

strSQL = "SELECT blah blah bla "
strSQL = strSQL & " FROM my table "

 

[Pat Hartman]

There are limits to strings in VBA. If you have run into one, then shorten your subform and control names. OR, use the multi-select combo which will produce a shorter string.

 

[MajP]

If you do not want to go multi select and keep the textboxes then you can tag all of the textboxes

Public Function GetFilter(frm As Access.Form, Optional isText As Boolean) As String
  Dim ctrl As Access.Control
  Dim val As String
  For Each ctrl In frm.Controls
    If ctrl.Tag = "Filter" Then
      If ctrl & "" <> "" Then
      val = ctrl.Value
      If isText Then val = "'" & val & "'"
      If GetFilter = "" Then
        GetFilter = val
      Else
        GetFilter = GetFilter & ", " & val
      End If
      End If
    End If
  Next ctrl
  GetFilter = "IN (" & GetFilter & ")"
End Function

DoCmd.OpenReport "rptBuyingList", acViewReport, , "STOCK_CAT " & GetFilter( Forms!frmBackground!subfrmBuyingList.form)

That should build a string like
STOCK_CAT IN (123, 456, 98, 70,....444)
set isText to true if you need something like
STOCK_CAT IN ("A","B","C"...)