Overcoming another author's failure to normalize.

[June7]

From post 3: The only approach I can think of is a parameterized query where the combobox is referenced under each of the 14 fields, each on a separate Criteria row to invoke the OR operator (looks like stair steps).

LIKE operator is not useful without wildcard. Might as well just use = sign.

The criteria under each RawPartNumber field would be:

LIKE "*" & [Enter the part number you are looking for] & "*"

Or if you use a textbox or combobox for user to input the value:

LIKE "*" & Forms!formname!comboboxname & "*"

[rpeare]

If you're going to use LIKE you have to provide wildcards or it will look for an exact match

Like "*" & [Enter the Part Number] & "*"

Then use the same criteria for each part number on your table, however, I do not think you can have 14 OR statements in a query (another reason to normalize even if it's painful)

The other option is to have an indicator field like

ShowMe: iif(instr(rawpartnumber1, "*" & [Enter the Part Number] & "*") > 0, 1, iif(instr(rawpartnumber2, "*" & [Enter the Part Number] & "*") > 0, 1, iif(instr(rawpartnumber3, "*" & [Enter the Part Number] & "*") > 0, 1,... and so on until part number 14)))

Then have a criteria of 1 in the ShowMe calculated value field.

[ch5150]

Okay! Now we are getting somewhere. Thank you very much! I did the Like query and it seems to be working. And it is clear that I will only be able to go out to RawPartNumber9... However, the normalization issue has been a greater challenge so far. I created a subform with a continuous form field attached via the work order number but it won't allow input until the new record has been created and that only occurs when the form is closed. My thought was to create a button on an existing search form to call up an existing review form having the underlying data filtered by the query. And rpeare, I am not sure about how to implement your suggestion regarding an indicator field.

[June7]

With a properly designed form/subform arrangement, the new record on main form will be committed to table when focus is shifted to the subform and thus allows data entry in the subform for related dependent records.

[ch5150]

I created a form based on the query. Opening the form prompts the user for the part number. It opens to show the work order and customer name for each matching record (The actual RawPartNumber fields are hidden). They then doubleclick the desired work order number and it opens the frmOperatorReports to the desired record. WAY TO GO!!!!! Thank you very much! I can't even say how much I appreciate your patience with me and how much I have learned even in this little bit of instruction. I would love to get to the point of normalizing the db but I was under the gun to provide a solution before the annual audit... And to that extent, Mission accomplished!