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

Pass string from VB Code into query.

ChaseCox
100+
P: 294
I currently have the following chunk of code that is building up a string of different 3-digit numbers:
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 would like to be able to filter my data based on the final value for strSize. Meaning they could choose any number of the coices above. I need to be able to pass strSize into the query so that i can filter my desired field which is:
Expand|Select|Wrap|Line Numbers
  1. [Generic Material].ORDERED_MODEL
  2.  
Feb 27 '07 #1
Share this Question
Share on Google+
6 Replies


NeoPa
Expert Mod 15k+
P: 31,308
Firstly, you can't do this in static SQL as the interpreter will treat the return value of the function as a single string, regardless of the contents :(
In VBA code you'll need something like :
Expand|Select|Wrap|Line Numbers
  1. Dim strSQL As String
  2. strSQL = "SELECT * " & _
  3.          "FROM [Generic Material] " & _
  4.          "WHERE [ORDERED_MODEL] In(" & _
  5.          Mid(MakeFilter2,2) & ")"
Feb 28 '07 #2

ChaseCox
100+
P: 294
Firstly, you can't do this in static SQL as the interpreter will treat the return value of the function as a single string, regardless of the contents :(
In VBA code you'll need something like :
Expand|Select|Wrap|Line Numbers
  1. Dim strSQL As String
  2. strSQL = "SELECT * " & _
  3.          "FROM [Generic Material] " & _
  4.          "WHERE [ORDERED_MODEL] In(" & _
  5.          Mid(MakeFilter2,2) & ")"
Ok, but how do I reference it in my query? I am at a complete loss on how to accomplish this.
Mar 1 '07 #3

NeoPa
Expert Mod 15k+
P: 31,308
Firstly, you can't do this in static SQL as the interpreter will treat the return value of the function as a single string, regardless of the contents :(
In VBA code you'll need something like :
Expand|Select|Wrap|Line Numbers
  1. Dim strSQL As String
  2. strSQL = "SELECT * " & _
  3.          "FROM [Generic Material] " & _
  4.          "WHERE [ORDERED_MODEL] In(" & _
  5.          Mid(MakeFilter2,2) & ")"
Ok, but how do I reference it in my query? I am at a complete loss on how to accomplish this.
As my answer covers that in full detail I'm not sure how I can respond.
What is it exactly you're confused about?
Mar 1 '07 #4

ChaseCox
100+
P: 294
As my answer covers that in full detail I'm not sure how I can respond.
What is it exactly you're confused about?
Do i need to reference this in my Query, or is this code already passing the value in?
Mar 1 '07 #5

NeoPa
Expert Mod 15k+
P: 31,308
Tell me about your query.
What do you use it for?
Where/how is it stored?
While you're about it, and potentially to save time later on, post your SQL too.

Depending on your requirements, this may not be possible (See post #2). If not, we'll see if we can find an alternative way to do what you need.
Mar 1 '07 #6

ChaseCox
100+
P: 294
Tell me about your query.
What do you use it for?
Where/how is it stored?
While you're about it, and potentially to save time later on, post your SQL too.

Depending on your requirements, this may not be possible (See post #2). If not, we'll see if we can find an alternative way to do what you need.
Thanks for the help guys. I got it figured out. My project is being controlled by 3 people, with different requests, so this project swings from day to day. So sorry i did not post back sooner.
Mar 6 '07 #7

Post your reply

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