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

Passing SQL "Like" string from VB code into Query Parameter.

ChaseCox
100+
P: 294
I have a Database Called FalconAnalysis.mdb. The table I am wanting to use is called Generic Material. The Query I want to pass the value into is called Material Query. The field in the query is called Order_Model. I would like to pass the result, "strSize", from the following string of code into the Order_Model field of Material Query, which looks at generic Material Table.
Expand|Select|Wrap|Line Numbers
  1. Private Sub MakeFilter2()
  2.  
  3. strSize = ""
  4.  
  5.     If Nz(chkv2125S, False) Then _
  6.             strSize = strSize & ",'150'"
  7.     If Nz(chkv2125H, False) Then _
  8.             strSize = strSize & ",'151'"
  9.     If Nz(chkv215S, False) Then _
  10.             strSize = strSize & ",'180'"
  11.     If Nz(chkv215H, False) Then _
  12.             strSize = strSize & ",'181'"
  13.     If Nz(chkv2175S, False) Then _
  14.             strSize = strSize & ",'210'"
  15.     If Nz(chkv2175H, False) Then _
  16.             strSize = strSize & ",'211'"
  17.     If Nz(chkv220S, False) Then _
  18.             strSize = strSize & ",'240'"
  19.     If Nz(chkv220H, False) Then _
  20.             strSize = strSize & ",'241'"
  21.     If Nz(chkv225S, False) Then _
  22.             strSize = strSize & ",'300'"
  23.     If Nz(chkv225H, False) Then _
  24.             strSize = strSize & ",'301'"
  25.  
  26.  
  27. End Sub
  28.  
I think it should be a like statment, any thoughts on how to do this?
Feb 6 '07 #1
Share this Question
Share on Google+
10 Replies


ADezii
Expert 5K+
P: 8,597
I have a Database Called FalconAnalysis.mdb. The table I am wanting to use is called Generic Material. The Query I want to pass the value into is called Material Query. The field in the query is called Order_Model. I would like to pass the result, "strSize", from the following string of code into the Order_Model field of Material Query, which looks at generic Material Table.
Expand|Select|Wrap|Line Numbers
  1. Private Sub MakeFilter2()
  2.  
  3. strSize = ""
  4.  
  5.     If Nz(chkv2125S, False) Then _
  6.             strSize = strSize & ",'150'"
  7.     If Nz(chkv2125H, False) Then _
  8.             strSize = strSize & ",'151'"
  9.     If Nz(chkv215S, False) Then _
  10.             strSize = strSize & ",'180'"
  11.     If Nz(chkv215H, False) Then _
  12.             strSize = strSize & ",'181'"
  13.     If Nz(chkv2175S, False) Then _
  14.             strSize = strSize & ",'210'"
  15.     If Nz(chkv2175H, False) Then _
  16.             strSize = strSize & ",'211'"
  17.     If Nz(chkv220S, False) Then _
  18.             strSize = strSize & ",'240'"
  19.     If Nz(chkv220H, False) Then _
  20.             strSize = strSize & ",'241'"
  21.     If Nz(chkv225S, False) Then _
  22.             strSize = strSize & ",'300'"
  23.     If Nz(chkv225H, False) Then _
  24.             strSize = strSize & ",'301'"
  25.  
  26.  
  27. End Sub
  28.  
I think it should be a like statment, any thoughts on how to do this?
Looks more like the In Construct to me:
strPassSQL = "In(" & strSize & ")"
Feb 6 '07 #2

ChaseCox
100+
P: 294
Looks more like the In Construct to me:
strPassSQL = "In(" & strSize & ")"
Does that go into the query, or the VB code?
Feb 6 '07 #3

Rabbit
Expert Mod 10K+
P: 12,315
Looks more like the In Construct to me:
strPassSQL = "In(" & strSize & ")"
You can't have this as the criteria of a query though. I don't think there's a way to run code which will change the criteria of a query from the query itself.

You'll most likely have to do it through a form.
Feb 6 '07 #4

ChaseCox
100+
P: 294
You can't have this as the criteria of a query though. I don't think there's a way to run code which will change the criteria of a query from the query itself.

You'll most likely have to do it through a form.
I have in the past set this value to a text box, and then past that value into the query. Is this the only way to do it?
Feb 6 '07 #5

Rabbit
Expert Mod 10K+
P: 12,315
As far as I know, yes.
Feb 6 '07 #6

ChaseCox
100+
P: 294
As far as I know, yes.
Thanks for the help Rabbit, much appreciated.
Feb 6 '07 #7

Rabbit
Expert Mod 10K+
P: 12,315
Not a problem. Glad to help.
Feb 6 '07 #8

NeoPa
Expert Mod 15k+
P: 31,186
Looks more like the In Construct to me:
strPassSQL = "In(" & strSize & ")"
Try instead using :
Expand|Select|Wrap|Line Numbers
  1. strPassSQL = "In(" & Mid(strSize,2) & ")"
To lose the first ',' character.
Feb 7 '07 #9

NeoPa
Expert Mod 15k+
P: 31,186
I have in the past set this value to a text box, and then past that value into the query. Is this the only way to do it?
It is also possible to set the SQL property of a QueryDef to match what you want.
Your code would have to be sophisticated enough to amend existing SQL.
Let me know if you're interested and/or want more detailed instructions.
Feb 7 '07 #10

nico5038
Expert 2.5K+
P: 3,072
Why not create a table with the 150, 151, 180, 181, etc. values in a row and an additional checkbox.
Thus you can let the user check the rows in a datasheet subform and all needed is to join this additional table by the code to this table and add the criteria "True" for the checkbox.

Saves the code and you could use a simple UPDATE query to set/reset the checkbox on the form.

Idea ?

Nic;o)
Feb 7 '07 #11

Post your reply

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