Variable subquery criteria SQL problems

[Ellsmasher]

I am having a tough time getting a nested/subquery to run that is part of an IIF statement (i.e. Select Query, with a subquery for criteria that is 'chosen' based on IIF statement logic).

I think it is a parsing issue as to how the IIF statement is returning the SQL subquery for the HAVING criteria:

SELECT tblProduct.CatNumber, tblProduct.ProductName, tblProduct.Strenth
FROM tblProduct INNER JOIN tblMMF ON tblProduct.CatNumber = tblMMF.CatNumber
GROUP BY tblProduct.CatNumber, tblProduct.ProductName, tblProduct.Strenth
HAVING (((tblProduct.CatNumber) In (IIf(1=1,"SELECT tblMMF.CatNumber FROM tblMMF INNER JOIN tblProduct ON tblMMF.CatNumber = tblProduct.CatNumber WHERE (((tblMMF.DocStatus) Not Like 'Deleted')) GROUP BY tblMMF.CatNumber;","1789"))));

My query executes as expected if I remove the IIF function, and just put In.. subquery.

Any ideas? Is there an issue with delimiting quotation marks a certain way?

FYI, I am using Access '97.

 

[Ellsmasher]

Well, it doesn't matter, I found a way to script an update to the querydef for the list I am building on a form. This works around my problem with needing a dynamic subquery which is determined based on the value of a parameter on the same form.