By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,491 Members | 1,868 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,491 IT Pros & Developers. It's quick & easy.

Single Criteria With Multiple Parameter Query With A Twist

P: 29
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):


Expand|Select|Wrap|Line Numbers
  1. 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


Expand|Select|Wrap|Line Numbers
  1. 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
  2.  
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.
Nov 10 '11 #1
Share this Question
Share on Google+
27 Replies


dsatino
100+
P: 393
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.
Nov 10 '11 #2

P: 29
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
Nov 10 '11 #3

dsatino
100+
P: 393
Open the query in SQL view and post that...it will definitely help for people to see the totality of what you're doing.
Nov 10 '11 #4

P: 29
Here is what I have so far, it just doesnt work where it is now

Expand|Select|Wrap|Line Numbers
  1. 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
  2. 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]
  3. 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))
  4. ORDER BY tbl_documents.Path;
  5.  
Nov 10 '11 #5

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

Expand|Select|Wrap|Line Numbers
  1. 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
  2. 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]
  3. 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))
  4. ORDER BY tbl_documents.Path;
  5.  
Nov 10 '11 #6

dsatino
100+
P: 393
Ok. Open your vba editor and open new module. Paste this in:

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. 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"
  5. 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]"
  6. Public Const sqlORDER As String = " ORDER BY docs.Path"
  7.  
  8.  
  9. Public Sub ReWriteQDef()
  10.     Dim qdef As QueryDef
  11.     Set qdef = CurrentDb.QueryDefs("FormQuery")
  12.     qdef.SQL = sqlSELECT & sqlFROM & sqlWHERE & sqlORDER
  13.     Forms!frm_docs_lookup_list.subfrm_docs_lookup_list.Requery
  14. End Sub
  15.  
  16.  
  17. Public Function sqlWHERE() As String
  18.     Dim frm As Form
  19.     Dim strTemp As String
  20.  
  21.     Set frm = Forms!frm_docs_lookup_list
  22.     strTemp = ""
  23.  
  24.     If ItemIsNull(frm!equip_sr) = False Then
  25.         strTemp = "(docs.title Like '*" & Trim(frm!equip_sr) & "*' OR docs.equip Like '*" & Trim(frm!equip_sr) & "*')"
  26.     End If
  27.  
  28.     If ItemIsNull(frm!po_sr) = False Then
  29.         If Len(strTemp) > 0 Then strTemp = strTemp & " AND "
  30.         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) & "*')"
  31.     End If
  32.  
  33.     If ItemIsNull(frm!vend_sr) = False Then
  34.         If Len(strTemp) > 0 Then strTemp = strTemp & " AND "
  35.         strTemp = strTemp & "(docs.vendor Like '*" & Trim(frm!vend_sr) & "*' OR docs.manufacturer Like '*" & Trim(frm!vend_sr) & "*')"
  36.     End If
  37.  
  38.     If Len(strTemp) > 0 Then
  39.         sqlWHERE = " WHERE " & strTemp
  40.     Else
  41.         sqlWHERE = ""
  42.     End If
  43.  
  44. End Function
  45.  
  46.  
  47. Public Function ItemIsNull(inputItem As Variant) As Boolean
  48.     If IsNull(inputItem) = True Then
  49.         ItemIsNull = True
  50.         Exit Function
  51.     End If
  52.  
  53.     If IsEmpty(inputItem) = True Then
  54.         ItemIsNull = True
  55.         Exit Function
  56.     End If
  57.  
  58.     If Trim(inputItem) = "" Then
  59.         ItemIsNull = True
  60.         Exit Function
  61.     End If
  62.  
  63.     ItemIsNull = False
  64. End Function
  65.  
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 "ReWriteQueryDef" 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
Nov 14 '11 #7

P: 29
when I try the ?sqlWHERE

it says 'ambiguous name detected'.
Nov 15 '11 #8

dsatino
100+
P: 393
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.
Nov 15 '11 #9

P: 29
In these lines, Im guessing I need to change the table names to the correct names? I just noticed that

Expand|Select|Wrap|Line Numbers
  1. 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" 
  2. 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]" 
  3. Public Const sqlORDER As String = " ORDER BY docs.Path"
Nov 15 '11 #10

dsatino
100+
P: 393
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.
Nov 15 '11 #11

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

dsatino
100+
P: 393
Yes, the full names were in your post, but I changed them to an alias. It will still work.

For example:
Expand|Select|Wrap|Line Numbers
  1. SELECT yt.AnyField1, yt.AnyField2 
  2. From YourTable yt
  3.  
is the same as

Expand|Select|Wrap|Line Numbers
  1. SELECT YourTable.AnyField1,YourTable.AnyField2 
  2. From YourTable 
  3.  
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.
Nov 15 '11 #13

P: 29
ok, nm that part. I got the module to work with the sqlWHERE fine. when, on the form, I add =ReWriteQueryDef() 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?
Nov 15 '11 #14

dsatino
100+
P: 393
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.
Nov 15 '11 #15

P: 29
I did that. I created a seperate module outside the form, a public one, and saved it as "ReWriteQueryDef".
Nov 15 '11 #16

dsatino
100+
P: 393
The name of the module has no relevance, "module1" would be fine. All the code I gave you has to be in a public module, the one you created is fine, after that it should work. But since I can't test it, the best I can do is help you with any errors.
Nov 15 '11 #17

P: 29
Ive tried

=ReWriteQDef
=ReWriteQDef()
ReWriteQDef
=ReWriteQueryDef
=ReWriteQueryDef()
ReWriteQueryDef

in the AfterUpdate event, and they all pretty much give the same error. Either it cant find the object, or the expression has a function name it cant find.
Nov 15 '11 #18

dsatino
100+
P: 393
Ah. I think I know what's going on.

Open the form in design view.
Right click the field(s) which you want to run the AfterUpdate.
Click properties.
Click the Event tab.
Click the AfterUpdate line. Remove anything you typed in it.
Click the "..." at the far right of the line.
Choose 'Code builder'

This will bring you to the vba editor and put you inside the AfterUpdate event sub of the control. Type in: ReWriteQueryDef
Nov 15 '11 #19

P: 29
when I do that, and enter a value in the field I get the following error "Expected variable or procedure, not module"
Nov 15 '11 #20

dsatino
100+
P: 393
That's probably because you name the module 'ReWriteQueryDef'. You need to change that. Pick anything.
Nov 15 '11 #21

P: 29
I just renamed it to "mod_ReWriteQueryDef". Should I put ReWriteQDef in the vba event, and not ReWriteQueryDef?
Nov 15 '11 #22

dsatino
100+
P: 393
I'm not sure what you're asking on that one....

As long as all the code I gave you is posted inside 'mod_ReWriteQueryDef' then just go back to post #19 and follow that.

So if you call the sub from the AfterUpdate event of the vend_sr box, the property line of the control will say "[Event Procedure]". If you click on the "..." button (code builder), you should see this code:

Expand|Select|Wrap|Line Numbers
  1. Private Sub vend_sr__AfterUpdate()
  2.      ReWriteQueryDef
  3. End Sub
  4.  
Nov 15 '11 #23

P: 29
I did all that. It still gives me the "Expected variable or procedure, not module" error. Is there a way for me to actually attach a stripped down copy of the db here so you can see what is going on?
Nov 16 '11 #24

P: 29
ok, I think I almost have it working. what I notices is, though, is that when I enter text in to one of the fields and it updates the subform results, the text I enter in to the field actually writes in to FormQuery's criteria fields. when I clear the fields on the main form, how can I get it to remove the criteria it set in to the query? also, it isnt completely displaying all the results in the subform display. Ill keep playing with the module, but basically the text I enter in to the form can be either in 1 of the query fields that is linked, a combination of them, all of them or none of them. if none of them, then it should display no results. I have to see if I can get this logic to work.
Nov 16 '11 #25

NeoPa
Expert Mod 15k+
P: 31,709
I don't plan to get involved here as it looks pretty involved already without my adding to that. What I will do, on a take it or leave it basis, is to drop in a couple of links which may help you understand the whole issue a little more clearly (or even find an alternative solution for your issue instead).

Example Filtering on a Form
Cascaded Form Filtering
Nov 17 '11 #26

dsatino
100+
P: 393
There is a way to attach a db, but I've never done it. I'm sure you'll have to zip it first. If you go ahead and do that, I'll take a look. At a minimum I'll be able to better understand what you're doing since I can actually see it.
Nov 18 '11 #27

NeoPa
Expert Mod 15k+
P: 31,709
See Attach Database (or other work) for how do do it properly ;-)
Nov 18 '11 #28

Post your reply

Sign in to post your reply or Sign up for a free account.