424,851 Members | 1,183 Online
+ 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.

 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 Private Sub MakeFilter2()   strSize = ""       If Nz(chkv2125S, False) Then _             strSize = strSize & ",'150'"     If Nz(chkv2125H, False) Then _             strSize = strSize & ",'151'"     If Nz(chkv215S, False) Then _             strSize = strSize & ",'180'"     If Nz(chkv215H, False) Then _             strSize = strSize & ",'181'"     If Nz(chkv2175S, False) Then _             strSize = strSize & ",'210'"     If Nz(chkv2175H, False) Then _             strSize = strSize & ",'211'"     If Nz(chkv220S, False) Then _             strSize = strSize & ",'240'"     If Nz(chkv220H, False) Then _             strSize = strSize & ",'241'"     If Nz(chkv225S, False) Then _             strSize = strSize & ",'300'"     If Nz(chkv225H, False) Then _             strSize = strSize & ",'301'"     End Sub   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 [Generic Material].ORDERED_MODEL   Feb 27 '07 #1
6 Replies

 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 Dim strSQL As String strSQL = "SELECT * " & _          "FROM [Generic Material] " & _          "WHERE [ORDERED_MODEL] In(" & _          Mid(MakeFilter2,2) & ")" Feb 28 '07 #2

 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 Dim strSQL As String strSQL = "SELECT * " & _          "FROM [Generic Material] " & _          "WHERE [ORDERED_MODEL] In(" & _          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

 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 Dim strSQL As String strSQL = "SELECT * " & _          "FROM [Generic Material] " & _          "WHERE [ORDERED_MODEL] In(" & _          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

 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

 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

 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