473,224 Members | 1,477 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,224 software developers and data experts.

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 1352
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

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

Similar topics

3
by: William C. White | last post by:
Does anyone know of a way to use PHP /w Authorize.net AIM without using cURL? Our website is hosted on a shared drive and the webhost company doesn't installed additional software (such as cURL)...
2
by: Albert Ahtenberg | last post by:
Hello, I don't know if it is only me but I was sure that header("Location:url") redirects the browser instantly to URL, or at least stops the execution of the code. But appearantely it continues...
3
by: James | last post by:
Hi, I have a form with 2 fields. 'A' 'B' The user completes one of the fields and the form is submitted. On the results page I want to run a query, but this will change subject to which...
0
by: Ollivier Robert | last post by:
Hello, I'm trying to link PHP with Oracle 9.2.0/OCI8 with gcc 3.2.3 on a Solaris9 system. The link succeeds but everytime I try to run php, I get a SEGV from inside the libcnltsh.so library. ...
1
by: Richard Galli | last post by:
I want viewers to compare state laws on a single subject. Imagine a three-column table with a drop-down box on the top. A viewer selects a state from the list, and that state's text fills the...
4
by: Albert Ahtenberg | last post by:
Hello, I have two questions. 1. When the user presses the back button and returns to a form he filled the form is reseted. How do I leave there the values he inserted? 2. When the...
1
by: inderjit S Gabrie | last post by:
Hi all Here is the scenerio ...is it possibly to do this... i am getting valid course dates output on to a web which i have designed ....all is okay so far , look at the following web url ...
2
by: Jack | last post by:
Hi All, What is the PHP equivilent of Oracle bind variables in a SQL statement, e.g. select x from y where z=:parameter Which in asp/jsp would be followed by some statements to bind a value...
3
by: Sandwick | last post by:
I am trying to change the size of a drawing so they are all 3x3. the script below is what i was trying to use to cut it in half ... I get errors. I can display the normal picture but not the...
0
by: veera ravala | last post by:
ServiceNow is a powerful cloud-based platform that offers a wide range of services to help organizations manage their workflows, operations, and IT services more efficiently. At its core, ServiceNow...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: mar23 | last post by:
Here's the situation. I have a form called frmDiceInventory with subform called subfrmDice. The subform's control source is linked to a query called qryDiceInventory. I've been trying to pick up the...
2
by: jimatqsi | last post by:
The boss wants the word "CONFIDENTIAL" overlaying certain reports. He wants it large, slanted across the page, on every page, very light gray, outlined letters, not block letters. I thought Word Art...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.