How to search multiple fields in multiple records

[StL Eng]

Thanks in advance for any help, I cannot seem to get it to work. I have a database of engineering project drawings where I catalogue the details used in the drawings such as pipe manufacturers, components, etc. I have 22 fields set up in each record that are named item 1, item 2, item 3 all the way up to item 22 and there are also 22 corresponding fields called item maker 1 - item maker 22. In the database record layout: item 1 input field is next to item maker 1 input field so that when I enter the record I can put corresponding data onto a line with 2 fields on it. For example I would enter flange pipe in item 1 and Chicago Steel in item maker 1. This would be for a flange pipe made by Chicago Steel that I used in the drawing. I am trying to figure out how to search all of the item 1 - item 22 fields in all of my records. For example if I want to try to fih=gure out in which drawings I used "flange pipe" I would like to be able to search for that in 1 place as opposed to having to go into find mode and enter it into 22 fields. I read two other posts which described creating a calculation field and concatenating the data by using : item 1 & "pr" & item 2 & "pr" & item 3, etc. For some reason when I search the calculation field it does not find the records. When I view the calculation field "box" in the layout it seems to combine all of the items for that record but has zeros between the items, such as: flange pipe00000elbow pipe000000, etc. Any help would be greatly appreciated!

[Ender]

For that combined calc, you should place separators between each item. Either a space or a ¶.

 

But you should really consider a relational structure here. This and other things will be much easier if you have your Items in a Line Item file related to your current Project file by ProjectID. With ony one set of Line Item fields, you can add any number of Line Items for each Project AND tie in costs like in an Invoice system.