Where Condition length too long

[jsimard]

I am trying to create a macro that will allow a user to enter some "search text" into a text field, hit the search button and only get the results they are looking for. I want to search the entire database not just certain fields. The apply filter macro was working perfectly for me until I reached 255 characters. This will not allow me to search all the fields required. Is there a way to search the entire database. I do not want to use the find feature as that still displays all the other records.

[June7]

By 'database' you mean a table?

You want to search for the same parameter in all fields? Why? What is the table structure?

Show example of filter criteria. Exactly how are you constructing this string?

I don't use macros, only VBA.

[jsimard]

The database contains all medical products used in the organization. Table fields include contract #, vendor name, brand name, product description etc. I would like the user to be able to search by any of these fields. They may want to search for "syringe", by a certain vendor or vendor code. I want one text box that will search all of my fields and only display results pertaining to their search.

[June7]

Still not sure how you are hitting 255 character limit.

I have one combobox for selecting the field to search. Then another combobox that lists data from selected field. Then a button to initiate search/filter. All is managed with VBA code.

[jsimard]

I reach the maximum 255 characters in the Macro: Apply Filter:
Where Condition = [Contract Desc] Like "*" & [Forms]![frmSearch]![Text99] & "*" Or [Vendor Name] Like "*" & [Forms]![frmSearch]![Text99] & "*" Or [Brand Name] Like "*" & [Forms]![frmSearch]![Text99] & "*"

[Perceptus]

you could change your where conditions to limit the amount of data your are checking for. like

WhereCondition$ = [Contract Desc] Like "*" & mid([Forms]![frmSearch]![Text99],1,100) & "*" Or [Vendor Name] Like "*" & mid([Forms]![frmSearch]![Text99],1,50) & "*" Or [Brand Name] Like "*" & mid([Forms]![frmSearch]![Text99],1,100) & "*"


Even this would hit the 255 character issue. as Len(WhereCondition$) is likely > 255 after evaluating.

[June7]

As noted, I would not use that methodology.

So no need to search by date or some numeric data?

If you don't want VBA, the only alternative I see is a dynamic parameterized query. Review http://datapigtechnologies.com/flash...tomfilter.html