SQL Error IN <subQuery>

[Tristan Rhodes]

I have the following section of code in an access database:

'Build Sub Query
str_SQL = "(SELECT Description.BasicDesc " _
& "FROM (Category INNER JOIN SubCategory ON Category.CategoryKey = SubCategory.CategoryKey) " _
& "INNER JOIN Description ON SubCategory.SubCategoryKey = Description.SubCategoryKey " _
& "WHERE (Category.CategoryDescription)='" & GetCurrCategory & "')"

'TO REMOVE
CurrentDb.QueryDefs.Delete "qdf_q"

'Construct sub query
Dim qdf_Query As QueryDef
Set qdf_Query = CurrentDb.CreateQueryDef("qdf_q", str_SQL)
DoCmd.OpenQuery ("qdf_q")

'Build outer query
str_SQL = "DELETE FROM Description " _
& "WHERE Description.BasicDesc " _
& "IN ( qdf_q.BasicDesc )"


The two queries are as follows:

SELECT Description.BasicDesc
FROM (Category INNER JOIN SubCategory ON Category.CategoryKey = SubCategory.CategoryKey)
INNER JOIN Description ON SubCategory.SubCategoryKey = Description.SubCategoryKey
WHERE (Category.CategoryDescription)= ' GetCurrCategory() ')

DELETE FROM Description
WHERE Description.BasicDesc
IN ( qdf_q.BasicDesc )

The first part, the SELECT statement works fine. This produces a populated query table, and I know it works.
However, when it reaches the outer DELETE command, it throws an error message:
Too Few Parameters : Expected 1.

If I nest the SELECT query in the DELETE query, it throws an error on INNER JOIN.

If i replace the sub query with a normal table, the query works fine.

I'm stumpped as to why this does not work. It's doing my head in. Both parts work fine seperately, but not together.

Any thoughts?

Tris

[juvamine]

excuse me i am french:
i hope i have understood your question:

try this:

DELETE FROM Description
WHERE Description.BasicDesc
IN (select BasicDesc from qdf_q)

i think that's all

bye
juvamine

[Tristan Rhodes]

THANKYOU! AWESOME!

You are a genius and I love you

Tris