Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


Tip of the Day
Language: Visual Basic
Expertise: Beginner
Oct 1, 1996

Creating Nested Queries

Question:
My question is with regards to VB's ability to handle complex SQL. I have the application I am developing set up in the following way: I copy the SQL created into the recordsource of a data control and then refresh the data control. The problem I am having is with SQL statements inside of SQL statements:

Example:

SELECT Work_Request_Number FROM Work_Request_File WHERE Work_Request_Number = (SELECT Work_Request_Number FROM [Work_Request/Problem_List] WHERE Problem_Number = (SELECT Problem_Number from Application_Error_Codes WHERE Application_Error_Code = "ABC"))

I have tried changing the quotes to single quotes, tried just one SQL statement inside another, etc. and SOMETIMES I get it to come up but most of the time I get an error code of either -3070 or 3000. I have looked in the VB manual and Error code 3000 is considered an "UNKNOWN ERROR".

Answer:
Try changing the equal sign to the word "IN", so the query looks like "SELECT fields FROM table WHERE ID IN (SELECT ID FROM TABLE WHERE OTHERID IN (SELECT OTHERID FROM TABLE2 WHERE OTHERID2 = 5)). That SQL code is ANSI standard, but it may be a little different for Access. Check the docs for the IN keyword.

DevX Pro
 
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap
Thanks for your registration, follow us on our social networks to keep up-to-date