ApplyFilter alternative?

[TKT_BEER]

Hey Guys!
I have a table that I need to search part numbers on.. for each single record it has multiple part number columns each named differently (PART01, PART01, PART03... PART10)

I am trying to do a search form to look in these different columns a specific part number per search.
I tried the ApplyFilter option running it thru a macro, but it doesnt give me enough caracter space to write the whole expression to look into the 10 columns, this is the expression I am using:

[PART01] Like "*" & [Forms]![FrmSearch]![Text47] & "*" Or [PART02] Like "*" & [Forms]![FrmSearch]![Text47] & "*"

So on, so forth.
I have been succesful with this option in searching the first 4 columns in each record, but the expression builder doesnt have more than 255 caracters to write on..
Is there a another way I can get this done? so each search is made in the 10 columns per record?
thx for all the help!

 

[alansidman]

I believe your issue is that your db is not normalized. Instead of having multiple fields of part numbers, you should have one field with part numbers and associate that with primary product. In this manner, you could query the one field for particular part number and find all products associated with the part number.

Here is a paper on the Fundamentals of Relationship Data Base Management Systems Design. I would look at this to understand how a DB should be set up.

 

[xenou]

Not sure if it's completely useless advice but you can just use the msaccess search box to search a table too.

Otherwise, you can try to use very short names for your form, table, and fields and try to fit everything into the limit.

 

[Micron]

Maybe more not-so-useful suggestion, given that I totally agree with the notion that the db isn't normalized, but if this is a query, switch to sql view and you will not have such a character limit. However, your sample might be an expression in a control row source, or a macro parameter (I don't use macros, thus can't recall what the limitations are for expressions or sql statements.

A sure fire test for lack of normalization is this: if you have to add a control to a form/report or field to a query, or field to a table because you need to add one more of example of the same thing (e.g.part number) then it's 99.9896% certain that the design lacks normalization. Of course, there is always that slight degree of uncertainty.

 

[stumac]

I agree with everyone else that this should be normalised.

However having been in this situation myself, here are a couple of suggestions:

a - build a union query for searching
b - concatenate your values for searching [Part01] & " " & [Part02] & " " & [Part03].... like "*" & [Forms]![FrmSearch]![Text47] & "*"

because you are using a wildcard search this should work.

 

[xenou]

b - concatenate your values for searching [Part01] & " " & [Part02] & " " & [Part03].... like "*" & [Forms]![FrmSearch]![Text47] & "*"

nice!