Single Criteria With Multiple Parameter Query With A Twist

[toonz]

I have a form (frm_docs_lookup_list) with 3 field controls: equip_sr, po_sr, vend_sr



the form has a subform attached to : subfrm_docs_lookup_list

the results in the subform display based on what text a user enters in to the controls on "frm_docs_lookup_list". multiple fields in the subform refer to each of the 3 search fields. those are listed below


equip_sr: Title, equip, notes
po_sr: [PO Number], Path
vend_sr: Vendor, Manufacturer


the current SQL doesnt work since the criteria statements I am using dont work right yet. Ill post the 2 versions of the statements Ive tried below, and explain what the goal is Im trying to achieve

first attempt (swap out vend_sr with the other names for the appropriate fields):

Like "*" & Trim([Forms]![frm_docs_lookup_list]![vend_sr]) & "*" Or ([Forms]![frm_docs_lookup_list]![vend_sr]) Is Null

Now, when I entered some text in "vend_sr" and in the query put the above string on 2 different lines in the query it worked. I get that and why. however, things start to get tricky for me here. 3 fields in the query look at 1 search control field, and the other 2 look at 2 each. at any given time, using the "vend_sr" control as an example, the Vendor or Manufacturer field could contain the text I enter into "vend_sr", they might not, or they both might. If neither do, then the subforms results would be empty (obviously). I tried using the code below in the criteria for Vendor and Manufacturer

Like "*" & Trim([Forms]![frm_docs_lookup_list]![vend_sr]) & "*" Or Not Like "*" & Trim([Forms]![frm_docs_lookup_list]![vend_sr]) & "*" or ([Forms]![frm_docs_lookup_list]![vend_sr]) Is Null

but that didnt work. it just displayed all records where neither Vendor or Manufacturer contain a NULL value. Its almost like the two fields need to look at each other to assist in what results to display. and then when the other fields in the query refer back to their specified form controls, they could also see the form field as NULL or 1 or more of those fields could or could not contain the text entered in to those fields.

example:

I know I have 2 records in my database where Vendor contains the text "fleet". No Manufacturer records contain that text. If I enter "fleet" in to the form field vend_sr, the subform should display both of those records, pulling the results based on the fact that field Vendor contains 2 records which have that text.

Another example. I have 13 records which contain the text "sunbelt". 12 of those records have "sunbelt" in the Vendor field, 11 of the 13 contain "sunbelt" in the Manufacturer field. the 13th record that didnt have the value in Vendor is one that has it in the Manufacturer field, so all the results are basically 1 has it, the other has it, or both have it at the same time.


How would I make a query understand that in a line of code for the criteria? This is driving me crazy. I spent all day yesterday trying to figure out why my form wasnt working, and when I did I got even more confused on how to fix it. Thank for all your help.

[June7]

Making my head spin! Can you provide the project for analysis?