Single Criteria With Multiple Parameter Query With A Twist

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • mindbenderz
    New Member
    • Oct 2011
    • 29

    Single Criteria With Multiple Parameter Query With A Twist

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

    the form has a subform attached to : subfrm_docs_loo kup_list

    the results in the subform display based on what text a user enters in to the controls on "frm_docs_looku p_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):


    Code:
    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


    Code:
    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.
  • dsatino
    Contributor
    • May 2010
    • 393

    #2
    I'm not going to attempt to code anything for you, but I will give you some advice that may be helpful...

    From the sound of it, you have numerous criteria that may or may not be used in the query. You have three combo boxes that may or may not produce several filter values each (I think).

    Stop tyring to make everything work at once. Start with the first box and code for that. Once that is working, add the next one and test for that, etc.

    Also, it appears you're just jamming all this garbage into the criteria line of a query. You're much better off dynamically generating the 'WHERE' statement of the sql that way you are asking for something specific in the query.

    Comment

    • mindbenderz
      New Member
      • Oct 2011
      • 29

      #3
      no combo boxes, just text fields on the form. something typed in to one or all of them fill filter the query more. I can get 1 to work, for the most part. but when I try making any of the others work it not only messes up the first one, but they also dont work. and I dont know how to code any of the stuff you mentioned. Im still learning vba and have quite a ways to go

      Comment

      • dsatino
        Contributor
        • May 2010
        • 393

        #4
        Open the query in SQL view and post that...it will definitely help for people to see the totality of what you're doing.

        Comment

        • mindbenderz
          New Member
          • Oct 2011
          • 29

          #5
          Here is what I have so far, it just doesnt work where it is now

          Code:
          SELECT tbl_documents.root_id, tbl_content_type.cont_type, tbl_documents.[Content Type], tbl_rigs.location, tbl_documents.[Assigned Location], tbl_documents.Title, tbl_documents.[Revision/Version Date], tbl_documents.[Asset Number], tbl_documents.Vendor, tbl_documents.Vend_ID, tbl_documents.Manufacturer, tbl_documents.Manuf_ID, tbl_documents.Model, tbl_documents.[Serial Number], tbl_documents.[PO Number], tbl_documents.[Pressure Rating], tbl_documents.Revision, tbl_documents.Path, tbl_asset_type.asset_subtype, tbl_documents.[Asset Type], tbl_documents.[PART NO], tbl_documents.[W/O & J/O NO], tbl_documents.modules, tbl_documents.RigID_Share, tbl_documents.[PKD Document Number], tbl_documents.qty, tbl_documents.Size, tbl_documents.[Destination Path], tbl_documents.XMIT, tbl_documents.DocID, tbl_documents.recvd_xmit, tbl_documents.draw_no, tbl_documents.[%_comp], tbl_documents.disc_type, tbl_documents.equip, tbl_documents.pkd_xmit, tbl_documents.tech_type, tbl_documents.[Received Date], tbl_documents.[End of Life Date], tbl_documents.[Supersede Date], tbl_documents.Version, tbl_documents.Ivara_Path, tbl_documents.[RO Number], tbl_documents.proj_name, tbl_documents.proj_phase, tbl_documents.issued_code, tbl_documents.approval_code, tbl_documents.owned, tbl_documents.[SO NO], tbl_documents.notes, tbl_documents.netpath, tbl_documents.user_add, tbl_documents.user_comp, tbl_documents.user_edit, tbl_documents.user_edit_date, tbl_documents.doc_filter, tbl_documents.date_added
          FROM tbl_rigs RIGHT JOIN (tbl_asset_type RIGHT JOIN (tbl_content_type RIGHT JOIN tbl_documents ON tbl_content_type.ID = tbl_documents.[Content Type]) ON tbl_asset_type.ID = tbl_documents.[Asset Type]) ON tbl_rigs.ID = tbl_documents.[Assigned Location]
          WHERE (((tbl_documents.Title) Like "*" & Trim([Forms]![frm_docs_lookup_list]![equip_sr]) & "*" Or (tbl_documents.Title) Is Null) AND ((tbl_documents.Vendor) Like "*" & Trim([Forms]![frm_docs_lookup_list]![vend_sr]) & "*" Or (tbl_documents.Vendor) Not Like "*" & Trim([Forms]![frm_docs_lookup_list]![vend_sr]) & "*" Or ([Forms]![frm_docs_lookup_list]![vend_sr]) Is Null) AND ((tbl_documents.Manufacturer) Like "*" & Trim([Forms]![frm_docs_lookup_list]![vend_sr]) & "*" Or (tbl_documents.Manufacturer) Not Like "*" & Trim([Forms]![frm_docs_lookup_list]![vend_sr]) & "*" Or ([Forms]![frm_docs_lookup_list]![vend_sr]) Is Null) AND ((tbl_documents.[PO Number]) Like "*" & Trim([Forms]![frm_docs_lookup_list]![po_sr]) & "*" Or (tbl_documents.[PO Number]) Is Null) AND ((tbl_documents.Path) Like "*" & Trim([Forms]![frm_docs_lookup_list]![po_sr]) & "*" Or (tbl_documents.Path) Is Null) AND ((tbl_documents.equip) Like "*" & Trim([Forms]![frm_docs_lookup_list]![equip_sr]) & "*" Or (tbl_documents.equip) Is Null) AND ((tbl_documents.notes) Like "*" & Trim(([tbl_documents].[notes])=[Forms]![frm_docs_lookup_list]![po_sr] & "*" Or ([tbl_documents].[notes])="*" & [Forms]![frm_docs_lookup_list]![equip_sr]) & "*" Or (tbl_documents.notes) Is Null))
          ORDER BY tbl_documents.Path;

          Comment

          • mindbenderz
            New Member
            • Oct 2011
            • 29

            #6
            Here is the last working version I have, just using criteria in the Vendor and Manufacturer fields of the query on different rows

            Code:
            SELECT tbl_documents.root_id, tbl_content_type.cont_type, tbl_documents.[Content Type], tbl_rigs.location, tbl_documents.[Assigned Location], tbl_documents.Title, tbl_documents.[Revision/Version Date], tbl_documents.[Asset Number], tbl_documents.Vendor, tbl_documents.Vend_ID, tbl_documents.Manufacturer, tbl_documents.Manuf_ID, tbl_documents.Model, tbl_documents.[Serial Number], tbl_documents.[PO Number], tbl_documents.[Pressure Rating], tbl_documents.Revision, tbl_documents.Path, tbl_asset_type.asset_subtype, tbl_documents.[Asset Type], tbl_documents.[PART NO], tbl_documents.[W/O & J/O NO], tbl_documents.modules, tbl_documents.RigID_Share, tbl_documents.[PKD Document Number], tbl_documents.qty, tbl_documents.Size, tbl_documents.[Destination Path], tbl_documents.XMIT, tbl_documents.DocID, tbl_documents.recvd_xmit, tbl_documents.draw_no, tbl_documents.[%_comp], tbl_documents.disc_type, tbl_documents.equip, tbl_documents.pkd_xmit, tbl_documents.tech_type, tbl_documents.[Received Date], tbl_documents.[End of Life Date], tbl_documents.[Supersede Date], tbl_documents.Version, tbl_documents.Ivara_Path, tbl_documents.[RO Number], tbl_documents.proj_name, tbl_documents.proj_phase, tbl_documents.issued_code, tbl_documents.approval_code, tbl_documents.owned, tbl_documents.[SO NO], tbl_documents.notes, tbl_documents.netpath, tbl_documents.user_add, tbl_documents.user_comp, tbl_documents.user_edit, tbl_documents.user_edit_date, tbl_documents.doc_filter, tbl_documents.date_added
            FROM tbl_rigs RIGHT JOIN (tbl_asset_type RIGHT JOIN (tbl_content_type RIGHT JOIN tbl_documents ON tbl_content_type.ID = tbl_documents.[Content Type]) ON tbl_asset_type.ID = tbl_documents.[Asset Type]) ON tbl_rigs.ID = tbl_documents.[Assigned Location]
            WHERE (((tbl_documents.Vendor) Like "*" & Trim([Forms]![frm_docs_lookup_list]![vend_sr]) & "*" Or ([Forms]![frm_docs_lookup_list]![vend_sr]) Is Null)) OR (((tbl_documents.Manufacturer) Like "*" & Trim([Forms]![frm_docs_lookup_list]![vend_sr]) & "*" Or ([Forms]![frm_docs_lookup_list]![vend_sr]) Is Null))
            ORDER BY tbl_documents.Path;

            Comment

            • dsatino
              Contributor
              • May 2010
              • 393

              #7
              Ok. Open your vba editor and open new module. Paste this in:

              Code:
              Option Compare Database
              Option Explicit
              
              Public Const sqlSELECT As String = "SELECT docs.root_id, cont.cont_type, docs.[Content Type], rigs.location, docs.[Assigned Location], docs.Title, docs.[Revision/Version Date], docs.[Asset Number], docs.Vendor, docs.Vend_ID, docs.Manufacturer, docs.Manuf_ID, docs.Model, docs.[Serial Number], docs.[PO Number], docs.[Pressure Rating], docs.Revision, docs.Path, asset.asset_subtype, docs.[Asset Type], docs.[PART NO], docs.[W/O & J/O NO], docs.modules, docs.RigID_Share, docs.[PKD Document Number], docs.qty, docs.Size, docs.[Destination Path], docs.XMIT, docs.DocID, docs.recvd_xmit, docs.draw_no, docs.[%_comp], docs.disc_type, docs.equip, docs.pkd_xmit, docs.tech_type, docs.[Received Date], docs.[End of Life Date], docs.[Supersede Date], docs.Version, docs.Ivara_Path, docs.[RO Number], docs.proj_name, docs.proj_phase, docs.issued_code, docs.approval_code, docs.owned, docs.[SO NO], docs.notes, docs.netpath, docs.user_add, docs.user_comp, docs.user_edit, docs.user_edit_date, docs.doc_filter, docs.date_added"
              Public Const sqlFROM As String = " FROM tbl_rigs rigs RIGHT JOIN (tbl_asset_type asset RIGHT JOIN (tbl_content_type cont RIGHT JOIN tbl_documents docs ON cont.ID = docs.[Content Type]) ON asset.ID = docs.[Asset Type]) ON rigs.ID = docs.[Assigned Location]"
              Public Const sqlORDER As String = " ORDER BY docs.Path"
              
              
              Public Sub ReWriteQDef()
                  Dim qdef As QueryDef
                  Set qdef = CurrentDb.QueryDefs("FormQuery")
                  qdef.SQL = sqlSELECT & sqlFROM & sqlWHERE & sqlORDER
                  Forms!frm_docs_lookup_list.subfrm_docs_lookup_list.Requery
              End Sub
              
              
              Public Function sqlWHERE() As String
                  Dim frm As Form
                  Dim strTemp As String
                  
                  Set frm = Forms!frm_docs_lookup_list
                  strTemp = ""
                  
                  If ItemIsNull(frm!equip_sr) = False Then
                      strTemp = "(docs.title Like '*" & Trim(frm!equip_sr) & "*' OR docs.equip Like '*" & Trim(frm!equip_sr) & "*')"
                  End If
                  
                  If ItemIsNull(frm!po_sr) = False Then
                      If Len(strTemp) > 0 Then strTemp = strTemp & " AND "
                      strTemp = strTemp & "(docs.[PO Number] Like '*" & Trim(frm!po_sr) & "*' OR docs.path Like '*" & Trim(frm!po_sr) & "*' OR docs.notes Like '*" & Trim(frm!po_sr) & "*')"
                  End If
                  
                  If ItemIsNull(frm!vend_sr) = False Then
                      If Len(strTemp) > 0 Then strTemp = strTemp & " AND "
                      strTemp = strTemp & "(docs.vendor Like '*" & Trim(frm!vend_sr) & "*' OR docs.manufacturer Like '*" & Trim(frm!vend_sr) & "*')"
                  End If
                  
                  If Len(strTemp) > 0 Then
                      sqlWHERE = " WHERE " & strTemp
                  Else
                      sqlWHERE = ""
                  End If
                  
              End Function
              
              
              Public Function ItemIsNull(inputItem As Variant) As Boolean
                  If IsNull(inputItem) = True Then
                      ItemIsNull = True
                      Exit Function
                  End If
                  
                  If IsEmpty(inputItem) = True Then
                      ItemIsNull = True
                      Exit Function
                  End If
                  
                  If Trim(inputItem) = "" Then
                      ItemIsNull = True
                      Exit Function
                  End If
                  
                  ItemIsNull = False
              End Function
              From there you need to write a query and save it with the name "FormQuery" . it doesn't matter what sql you put in it.

              Change the recordsource of your subform to "FormQuery" .

              You then need to call the sub "ReWriteQueryDe f" from somewhere. You can either put it in the 'Afterupdate' event of each of your lookup boxes or you can add a button to your main form that the user pushes.

              You can test the syntax of the WHERE statement in the VBA editor as follows:
              Open your form. Type some stuff in the boxes. in the VBA editor immediate pane, type: ?sqlWHERE
              Press enter

              Comment

              • mindbenderz
                New Member
                • Oct 2011
                • 29

                #8
                when I try the ?sqlWHERE

                it says 'ambiguous name detected'.

                Comment

                • dsatino
                  Contributor
                  • May 2010
                  • 393

                  #9
                  usually that means you have two functions with the same name...did you paste it twice?

                  From the DBA Editor click 'debug', then compile. It should call out ambiguities.

                  Comment

                  • mindbenderz
                    New Member
                    • Oct 2011
                    • 29

                    #10
                    In these lines, Im guessing I need to change the table names to the correct names? I just noticed that

                    Code:
                    Public Const sqlSELECT As String = "SELECT docs.root_id, cont.cont_type, docs.[Content Type], rigs.location, docs.[Assigned Location], docs.Title, docs.[Revision/Version Date], docs.[Asset Number], docs.Vendor, docs.Vend_ID, docs.Manufacturer, docs.Manuf_ID, docs.Model, docs.[Serial Number], docs.[PO Number], docs.[Pressure Rating], docs.Revision, docs.Path, asset.asset_subtype, docs.[Asset Type], docs.[PART NO], docs.[W/O & J/O NO], docs.modules, docs.RigID_Share, docs.[PKD Document Number], docs.qty, docs.Size, docs.[Destination Path], docs.XMIT, docs.DocID, docs.recvd_xmit, docs.draw_no, docs.[%_comp], docs.disc_type, docs.equip, docs.pkd_xmit, docs.tech_type, docs.[Received Date], docs.[End of Life Date], docs.[Supersede Date], docs.Version, docs.Ivara_Path, docs.[RO Number], docs.proj_name, docs.proj_phase, docs.issued_code, docs.approval_code, docs.owned, docs.[SO NO], docs.notes, docs.netpath, docs.user_add, docs.user_comp, docs.user_edit, docs.user_edit_date, docs.doc_filter, docs.date_added" 
                    Public Const sqlFROM As String = " FROM tbl_rigs rigs RIGHT JOIN (tbl_asset_type asset RIGHT JOIN (tbl_content_type cont RIGHT JOIN tbl_documents docs ON cont.ID = docs.[Content Type]) ON asset.ID = docs.[Asset Type]) ON rigs.ID = docs.[Assigned Location]" 
                    Public Const sqlORDER As String = " ORDER BY docs.Path"

                    Comment

                    • dsatino
                      Contributor
                      • May 2010
                      • 393

                      #11
                      Usually. But since you supplied the SQL I used the table names you had in there. I did 'alias' all of them though so as to cut down on the lenghth of the SQL.

                      Comment

                      • mindbenderz
                        New Member
                        • Oct 2011
                        • 29

                        #12
                        the sql in my posts showed the tables full names. at least thats what I can see (i.e. tbl_documents, tbl_content_typ es, etc). I guess there is a character limited on the vba lines. does it allow carriage returns in the code?

                        Comment

                        • dsatino
                          Contributor
                          • May 2010
                          • 393

                          #13
                          Yes, the full names were in your post, but I changed them to an alias. It will still work.

                          For example:
                          Code:
                          SELECT yt.AnyField1, yt.AnyField2 
                          From YourTable yt
                          is the same as

                          Code:
                          SELECT YourTable.AnyField1,YourTable.AnyField2 
                          From YourTable
                          which is what i did to the SQL i gave you.

                          I wrote that in the VBA editor, so if there is a character limitation per line, it doesn't affect this. It's poor practice to give it to you like that, but it doesn't affect the functionality in any way.

                          Any yes, the underscore "_" character is what the VBA editor will recognize for that purpose.

                          Comment

                          • mindbenderz
                            New Member
                            • Oct 2011
                            • 29

                            #14
                            ok, nm that part. I got the module to work with the sqlWHERE fine. when, on the form, I add =ReWriteQueryDe f() to AfterUpdate for the 3 fields, it says "The expresion you entered has a function name that Database can't find". Did I call it wrong in the event lines?

                            Comment

                            • dsatino
                              Contributor
                              • May 2010
                              • 393

                              #15
                              That usually means that the function is in the form module rather than a public module. Just cut the code out of the form, create a new module, and paste it there instead.

                              Comment

                              Working...