Need help with nested select - Xtreme Visual Basic Talk

09-10-2005, 10:52 PM

vb_beginnerdude vb_beginnerdude is offline

Newcomer

Join Date: Aug 2005

Posts: 2

Need help with nested select

Hi,

As this is my first post, I'll do a brief introduction about myself

I'm currently studying in Malaysia for my IT diploma certificate. Curently, I'm trying to complete a year-end software development project and am stuck with an sql query statement. I did a search through the previous posts in this forum for a solution but couldn't manage to find a way to solve it.

Here's the situation. There are two tables; bookingfile and schedulefile. I need to select a field, ScheduleNo, in the schedulefile, which will be on a certain date.
Then select a few fields which have the same corresponding ScheduleNo in the bookingfile table.

Here's the current query:

Code:

strsql = "SELECT ScheduleNo, BusNo FROM BookingFile WHERE ScheduleNo IN" _ 
& "(SELECT ScheduleNo FROM ScheduleFile WHERE Day(EstimatedDepartureDate) = '" & query_date & "' AND Month(EstimatedDepartureDate) = '" & query_month & "'  " _
& " AND Year(EstimatedDepartureDate) = '" & query_year & "')"

The inner select seems to be working because I'm getting getting the appropriate schedule numbers from the dates specified. But when combined witht outer select, it gave out this error: " Invalid Memo, OLE, or Hyperlink Object in subquery 'ScheduleNo'. "

The database file used is access.

I would greatly appreciate any help from you guys. Thanks.

Last edited by Shurik12; 09-11-2005 at 02:57 PM.

09-11-2005, 03:02 PM

Shurik12

Steppe Walker

Retired Moderator
* Expert *

Join Date: Jul 2002

Location: Ukraine/Russia/Belgium

Posts: 7,227

Hi,

Welcome.

Could you add

Debug.Print strsql to your code after the parameters have been passed and show the result here.

Shurik.

__________________
"A diaper is not like a computer that makes satisfying burbling noises from time to time, hinting at great inner complexity." Malcolm Gladwell

"I'm sitting here completely surrounded by no beer." Onslow, 'Keeping up appearances'

09-11-2005, 07:57 PM

vb_beginnerdude vb_beginnerdude is offline

Newcomer

Join Date: Aug 2005

Posts: 2

Quote:

Originally Posted by Shurik12

Hi,

Welcome.

Could you add

Debug.Print strsql to your code after the parameters have been passed and show the result here.

Shurik.

I did that and here's the result:

Select ScheduleNo from BookingFile WHERE ScheduleNo IN (Select ScheduleNo from ScheduleFile where Day(EstimatedDepartureDate) = '28' And Month(EstimatedDepartureDate) = '6' And Year(EstimatedDepartureDate) = '2005')

Thanks.

09-11-2005, 09:32 PM

NEOLLE

fully realized avatar

Super Moderator
* Expert *

Join Date: Jun 2004

Location: Davao Philippines

Posts: 2,295

Hi,
Welcome!

Quote:

Originally Posted by vb_beginnerdude

error: " Invalid Memo, OLE, or Hyperlink Object in subquery 'ScheduleNo'"

As far as I know, you can not use the following datatypes in the WHERE clause. Try to re-eveluate your table design and use the appropriate datatype for each field.

__________________
A Day Called Today The Avatar State Amazing Davao! Shared Pool

09-11-2005, 10:03 PM

webbone

Hydrogen Powered

Administrator
* Expert *

Join Date: Jul 2003

Location: Sacramento, CA

Posts: 6,090

I believe your problem is a datatype issue - specifically you are surrounding the Day, Month and Year parameters (as returned by those functions) with ' symbols - those functions return Variants of subtype Integer which would (I think) classify them as numeric parameters.

Remove the ' symbols like this and it should work:

Code:

strsql = "SELECT ScheduleNo, BusNo FROM BookingFile WHERE ScheduleNo IN" _ 
& "(SELECT ScheduleNo FROM ScheduleFile WHERE Day(EstimatedDepartureDate) = " & query_date & " AND Month(EstimatedDepartureDate) = " & query_month & "  " _
& " AND Year(EstimatedDepartureDate) = " & query_year & ")"

If that works, you might ask why wasn't the error message more one point - unfortunately Access (Jet) frequently returns somewhat ambiguous error messages (such as the famous "A multi-step error has occurred") when a subquery or evaluated item causes the error.

__________________
"With the appearance of the AddressOf operator, an entire industry has developed among authors illustrating how to do previously impossible tasks using Visual Basic. Another industry is rapidly developing among consultants helping users who have gotten into trouble attempting these tasks." -Dan Appleman