Access 97 Sub Query Error

S

steve

Hi All

Was wondering if anyone is able to help on the below query:

dbC.Execute "INSERT INTO dt_local " & _
"SELECT TOP 1 * " & _
"FROM (SELECT hh.* " & _
"FROM dt_pending as hh " & _
"WHERE (((hh.di_release) = 'Y') And ((hh.di_campaign) = '"
& pool & "')) " & _
"ORDER BY hh.di_priority, hh.di_workorder);", dbFailOnError

'pool' is a value on the form supplied to the user when the database
opens. when this query is run as it is i get a 3131 "Syntax error in
FROM clause"

The query works as a non subquery but produces more than one value as
there is similarity between results.

Before I was reading the form value i just used the following

a) (as a stored query)
SELECT dt_pending.*
FROM dt_pending
WHERE (((dt_pending.di_release)="Y"))
ORDER BY dt_pending.di_priority, dt_pending.di_workorder;

and then

b) (query in code) - where work_order is stored query
dbC.Execute "INSERT INTO dt_local " & _
"SELECT Top 1 work_order.* " & _
"FROM work_order;", dbFailOnError

but the code query was not able to evaluate the form value when this
was intreoduced to the first query. ie when query A was changed to
this:

SELECT dt_pending.*, dt_pending.di_campaign
FROM dt_pending
WHERE (((dt_pending.di_release)="Y") AND
((dt_pending.di_campaign)=[forms].[startup].[cbopool]))
ORDER BY dt_pending.di_priority, dt_pending.di_workorder;

it would not work

The top code works on 2003 but not 97.

Is there any alternate suggestions? Perhaps a way of enabling the coded
query b to evaluate the expression in the stored query?
 
Top