Is there a reason you're not passing the tripDate
of the report you are viewing as the variable, rather
than the reportID (assuming that since you are able
to retrieve the ID, you are also able to retrieve
the date ... )
The query to get the previous/next could then
look something like the following:
SELECT reportID,tripDate
FROM beaverReports
WHERE
(
tripDate > #variables.thisDate#
OR tripDate < #variables.thisDate#
OR tripDate = #variables.thisDate#
)
ORDER BY tripDate;
Add whatever limit you wish, and that should work ...
Just a thought, and not a deeply contemplated one at that =0)
Anyway, in answer to your initial question, it looks like there is a paren
problem ...
Try this:
(
SELECT TOP 1 reportID
FROM beaverReports
WHERE tripDate >
(
SELECT tripDate FROM beaverReports
WHERE reportID = #variables.thisReport#
)
UNION SELECT reportID
FROM beaverReports
WHERE reportID = #variables.thisReport#
)
UNION SELECT TOP 1 reportID
FROM beaverReports
WHERE tripDate <
(
SELECT tripDate FROM beaverReports
WHERE reportID = #variables.thisReport#
)
'There was an additional paren here, i removed it
' from this, rather than above, not sure which you
' want to keep
ORDER BY tripDate
Jason Ross
> On Fri, 17 Oct 2003, Paul Giesenhagen wrote:
>
> (
> SELECT TOP 1 reportID
> FROM beaverReports
> WHERE tripDate >
> (SELECT tripDate FROM beaverReports WHERE reportID =
#variables.thisReport#)
> UNION
> SELECT reportID
> FROM beaverReports
> WHERE reportID = #variables.thisReport#)
> UNION
> SELECT TOP 1 reportID
> FROM beaverReports
> WHERE tripDate <
> (SELECT tripDate FROM beaverReports WHERE reportID =
#variables.thisReport#)
>
> )
> ORDER BY tripDate
>
>
> ERROR:
>
> ODBC Error Code = 37000 (Syntax error or access violation)
>
>
> [Microsoft][ODBC SQL Server Driver][SQL Server]Line 7: Incorrect syntax
near ')'.
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
