Macro character limitation

[Hossein]

Hi dear friends

I use below code in macro for filtering columns but macro has not accepted more than 255 character.


How can do it ?

[CompanyLE] Like "*" & [Forms]![Search_PTP]![Search_Company] & "*" AND 
[SBU] Like "*" & [Forms]![Search_PTP]![Search_SBU] & "*" AND 
[Oerder_NO] Like "*" & [Forms]![Search_PTP]![Search_PO] & "*" AND 
[Order_Date] Like "*" & [Forms]![Search_PTP]![OrderDate_Search] & "*" AND 
[KhorshidiDate] Like "*" & [Forms]![Search_PTP]![GOD_Search] & "*" AND 
[Note] Like "*" & [Forms]![Search_PTP]![Note_Serch] & "*" AND 
[Currency] Like "*" & [Forms]![Search_PTP]![Currency_Search] & "*" AND 
[Payment_Term] Like "*" & [Forms]![Search_PTP]![PaymentTerm_Search] & "*" AND 
[IDH_Code] Like "*" & [Forms]![Search_PTP]![IDHCode_Search] & "*" AND 
[Item Description] Like "*" & [Forms]![Search_PTP]![ItemDescription_search] & "*"

[CJ_London]

you can't - see this link https://www.databasezone.com/techdocs/acclimit.html

255 is the max for any entry in a macro

you will need to convert your macro to vba code

[Hossein]

you can't - see this link https://www.databasezone.com/techdocs/acclimit.html

255 is the max for any entry in a macro

you will need to convert your macro to vba code

Thanks a lot.

[Hossein]

you can't - see this link https://www.databasezone.com/techdocs/acclimit.html

255 is the max for any entry in a macro

you will need to convert your macro to VBA code

I am writing VBA code but i have same issue !!
how can i move to next row ?

[CJ_London]

you need to assign the code to a string and build it

dim filterstr as string
filterstr="[CompanyLE] Like '*" & [Forms]![Search_PTP]![Search_Company] & "*' AND.....

note the use of single and double quotes to build the string

to go to the next line use the underscore e.g.

filterstr="[CompanyLE] Like '*" & _
                    [Forms]![Search_PTP]![Search_Company] & "*' AND.....

also, if this code is running in an event in your search_ptp form, you can replace [Forms]![Search_PTP]! with Me. e.g. me.search_company

[Hossein]

Dear my friends
I could found solution !
For going to next line you can follow below example :

.Formula = Array("=IF(OR(ISNUMBER(SEARCH(""DC-9"",$E2)),ISNUMBER(SEARCH(""DC9"",$E2))),""DC-9"","""")", "=IF(OR(ISNUMBER(SEARCH(""DC-9-11"",$E2)),..... _
ISNUMBER(SEARCH(""DC-9,-11"",$E2))),""DC-9"","""")")

change to :

    With rngTest
        .Formula = Array("=IF(OR(ISNUMBER(SEARCH(""DC-9"",$E2))," & _
        "ISNUMBER(SEARCH(""DC9"",$E2))),""DC-9"","""")", _
        "=IF(OR(ISNUMBER(SEARCH(""DC-9-11"",$E2))," & _
        "ISNUMBER(SEARCH(""DC-9,-11"",$E2))),""DC-9"","""")")
    End With

[Hossein]

Many thanks

[Hossein]

This is my final code

Private Sub Command1236_Click()

DoCmd.ApplyFilter "Companyf", "[CompanyLE] Like ""*"" & [Forms]![Search_PTP]![Search_Company] & ""*"" and " & _
"[SBU] Like ""*"" & [Forms]![Search_PTP]![Search_SBU] & ""*"" and " & _
"[Oerder_NO] Like ""*"" & [Forms]![Search_PTP]![Search_PO] & ""*"" and " & _
"[Order_Date] Like ""*"" & [Forms]![Search_PTP]![OrderDate_Search] & ""*"" and " & _
"[KhorshidiDate] Like ""*"" & [Forms]![Search_PTP]![GOD_Search] & ""*"" and " & _
"[Note] Like ""*"" & [Forms]![Search_PTP]![Note_Serch] & ""*"" and " & _
"[Currency] Like ""*"" & [Forms]![Search_PTP]![Currency_Search] & ""*"" and " & _
"[Payment_Term] Like ""*"" & [Forms]![Search_PTP]![PaymentTerm_Search] & ""*"" and " & _
"[IDH_Code] Like ""*"" & [Forms]![Search_PTP]![IDHCode_Search] & ""*"" and " & _
"[IDH_Name] Like ""*"" & [Forms]![Search_PTP]![ItemDescription_search] & ""*"" and " & _
"[RMP] Like ""*"" & [Forms]![Search_PTP]![RMP_search] & ""*"" and " & _
"[QTY] Like ""*"" & [Forms]![Search_PTP]![InvQuantity_search] & ""*"" and " & _
"[Unit_Price] Like ""*"" & [Forms]![Search_PTP]![UnitPrice_search] & ""*""", ""


End Sub

[CJ_London]

so VBA wasn't so bad!

A couple of comments - name your controls with something meaningful - you will have forgotten what Command1236 is when you revisit your code some time in the future.

you seem to be searching numeric values with Like - e.g. qty, price, which seems a bit odd to me - if you search for 12, you will also get 121, 1200, 1012, is that what you want?

Also, Note and Currency are reserved words, using them as a field names is likely to cause strange error messages somewhere down the line - see this link for reserved words https://support.office.com/en-us/art...7-da237c63eabe