Dear List
I have 2 tables: Stop (which holds details of bus stops) and
RZStop (which links stops to certain Routes and the
geographical Zones through which the Route passes. The user selects a
route to plot on the map. Time was when all Stop numbers were unique,
but I later found that different counties can have the same stop nos,
and, of course, a bus route can cross county boundaries. So a County ID
had to be added to both tables.
The SQL below is to get all the Stop details for each stop on a route,
so that the stops can be plotted on the map with the Route (which is
displayed as a series of contiguous polylines). It used to work until I
added the "and RZStop.County = Stop.County" line, and now I get the
error message:
"Variable or field County not defined. Subquery was invalid or did not
return any values"
I can sort of figure out why it says this about the subquery, in that it
hasn't yet got the Stop.county with which to compare teh RZStop.county.
But, the SQL works in Visual Foxpro (slightly syntactically altered to
suit the language). Also, I can't figure out how to break this down to,
say, a query followed by looping through it to get the Stop details, due
to the short-comings of the VB language.
Can anyone please help?
'ppreciate it
Terry McDonnell
--------------------------------------------
Dim lcRouteNo, lcGen, lcDirectn as String, lnRowID as SmallInt
lcRouteNo = RTRIM$( UCASE$( gcRouteNo))
lcGen = UCASE$( gcGen)
lcDirectn = UCASE$( gcDirectn)
Print "Please wait. Finding Stops for selected Route ..."
Select * from Stop
where stop.Stop_no in
( Select Stop, County
from RZStop
where LTRIM$( RTRIM$( RZStop.Rte_No)) = lcRouteNo
and RZStop.Rte_Let = lcGen
and RZStop.Directn = lcDirectn
and RZStop.County = Stop.County
and RZStop.SeqNo = 1)
into csrStops NoSelect ' Route's Stops only
-------------------------------------------------
