469,097 Members | 1,388 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,097 developers. It's quick & easy.

In

3
Beginner looking for help.

I have a unlimitted amount of values that i would like to compare against a field in a database. I was told to use an "IN" stetement against a string. I cant figure out the correct syntax


"where table.field in (field1, field2, field3) ")

whats the correct syntax for Visual Basic?
Nov 18 '06 #1
3 1214
willakawill
1,646 1GB
Beginner looking for help.

I have a unlimitted amount of values that i would like to compare against a field in a database. I was told to use an "IN" stetement against a string. I cant figure out the correct syntax


"where table.field in (field1, field2, field3) ")

whats the correct syntax for Visual Basic?
Hi. You don't need to figure out any syntax for any language. It is always right there in 'help' or 'books on line'.

The syntax of IN is not part of visual basic. It is SQL.
When you are comparing a field to a list of values with the IN operator you can do one of 2 things. Either you supply the list of values or it comes from a SELECT clause. The SELECT clause can only return one field. In both cases the type of value returned eg. string, must match the type of the field that you are testing.

Expand|Select|Wrap|Line Numbers
  1. SELECT *
  2. FROM table1
  3. WHERE field1 IN (
  4.   33
  5. , 43
  6. , 22
  7. , 56
  8. , 132
  9. )
  10. 'field1 must be an integer type
  11.  
  12. 'or slightly more involved
  13. SELECT *
  14. FROM table1
  15. WHERE field1 IN (
  16. SELECT name
  17. FROM Customer
  18. WHERE zip IN (
  19.   60606
  20. , 60665
  21. , 60065
  22. ))
  23. 'field1 must be a string type and zip must be an integer
  24.  
When you are writing SQL I suggest that you do it in this format because it will save you a lot of agony in debugging. Hope this helps :)
Nov 18 '06 #2
or1980
3
Hello and thank you for your help.
I figured out in part now i have a problem with building my string
currently i have what you suggested and its working
my example:
"where SOHeader.OrdNbr in ('AL000005', 'AL000006') ")

I have an indefinite number of order numbers and i'm trying to build a string to put into my sql and i cant figure that part out. I have the building of the string inside a do while loop thats going to the excel spreadsheet which i can move through till the end of the file or while its not empty thats not the problem i cant seem to put a correct syntax string inside the in statement

"where SOHeader.OrdNbr in string ") <--??? how do i make my string = ('AL000005', 'AL000006')
Nov 18 '06 #3
willakawill
1,646 1GB
Hello and thank you for your help.
I figured out in part now i have a problem with building my string
currently i have what you suggested and its working
my example:
"where SOHeader.OrdNbr in ('AL000005', 'AL000006') ")

I have an indefinite number of order numbers and i'm trying to build a string to put into my sql and i cant figure that part out. I have the building of the string inside a do while loop thats going to the excel spreadsheet which i can move through till the end of the file or while its not empty thats not the problem i cant seem to put a correct syntax string inside the in statement

"where SOHeader.OrdNbr in string ") <--??? how do i make my string = ('AL000005', 'AL000006')
Hi when you are building your string in a loop, do it like this:
Expand|Select|Wrap|Line Numbers
  1. strMyString = strMyString & "'" & inputfromexcel & "'"
  2.  
And then in the SQL statement
Expand|Select|Wrap|Line Numbers
  1. "WHERE SOHeader.OrdNbr IN (" & strMyString & ")"
  2.  
Nov 19 '06 #4

Post your reply

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

Similar topics

3 posts views Thread by William C. White | last post: by
2 posts views Thread by Albert Ahtenberg | last post: by
3 posts views Thread by James | last post: by
reply views Thread by Ollivier Robert | last post: by
1 post views Thread by Richard Galli | last post: by
4 posts views Thread by Albert Ahtenberg | last post: by
1 post views Thread by inderjit S Gabrie | last post: by
2 posts views Thread by Jack | last post: by
3 posts views Thread by Sandwick | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by kglaser89 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.