In

[or1980]

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?

[willakawill]

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.

SELECT *
FROM table1
WHERE field1 IN (
  33
, 43
, 22
, 56
, 132
)
'field1 must be an integer type

'or slightly more involved
SELECT *
FROM table1
WHERE field1 IN (
SELECT name
FROM Customer
WHERE zip IN (
  60606
, 60665
, 60065
))
'field1 must be a string type and zip must be an integer

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 :)

[or1980]

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')

[willakawill]

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:

strMyString = strMyString & "'" & inputfromexcel & "'"

And then in the SQL statement

"WHERE SOHeader.OrdNbr IN (" & strMyString & ")"