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

Parameter Using In List In SQL Qualifier

P: 2
Hi

I am using VBA to execute access queries, all works fin but cannot pass in more that one value in my parameter#

Select * from table where myvalue in ([myparm]);

this workd great for a single value : value01
But will not accept : value01,value02,Value03

I have tried many combinations of single and double quotes but with no sucess
Aug 9 '09 #1
Share this Question
Share on Google+
3 Replies


ADezii
Expert 5K+
P: 8,679
@CAPETOWNANDRE
Expand|Select|Wrap|Line Numbers
  1. SELECT *
  2. FROM Employees
  3. WHERE Employees.Region In ('NJ','PA','WA');
Aug 9 '09 #2

P: 2
Yep, that will work , but when you want to send the values as a parameter
MYPArm = " 'NJ','PA','WA ' "

select * from Employees where employees.region in ([myparm]);

It wont work
Aug 9 '09 #3

ADezii
Expert 5K+
P: 8,679
@CAPETOWNANDRE
I tested the following code that I created and it does, in fact, work. Is this similar to what you are looking for?
Expand|Select|Wrap|Line Numbers
  1. Dim MyDB As DAO.Database
  2. Dim rstTest As DAO.Recordset
  3. Dim strSQL As String
  4. Dim strParam As String
  5.  
  6. strParam = "'NJ','PA','WA'"
  7.  
  8. strSQL = "SELECT *FROM Employees WHERE Employees.Region In (" & strParam & ");"
  9.  
  10. Set MyDB = CurrentDb
  11. Set rstTest = MyDB.OpenRecordset(strSQL, dbOpenForwardOnly)
  12.  
  13. With rstTest
  14.   Do While Not .EOF
  15.     MsgBox ![FirstName] & " " & ![LastName]
  16.     .MoveNext
  17.   Loop
  18. End With
  19.  
  20. rstTest.Close
  21. Set rstTest = Nothing
Aug 9 '09 #4

Post your reply

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