Q: Subqueries in CDaoRecordset::FindFirst()

[Jack Shainsky]

Hello!
In my application I would like to find records in Access DB, matching SQL statement like this :

SELECT * FROM Products
WHERE ProductID IN
(SELECT ProductID FROM OrderDetails
WHERE Discount >= .25);

(This is the sample from the Microsoft Jet Database SQL reference).

I have the open recordset binded to Products table, so I'm trying to search like this :


rsProducts.FindFirst("ProductID IN (SELECT ProductID FROM OrderDetails WHERE Discount >= .25)");




But when I execute this code, it throws DAO exception
"The Microsoft Jet database engine does not recognize ' (SELECT ProductID FROM OrderDetails WHERE Discount >= .25)' as a valid field name or expression."

I checked the SQL statement in the Access, and it works fine there. What should I fix in my program(I don't want requery recordset with the new query, because I want to use it for other purposes too)?

Thank you in advance,

Jack Shainsky
Jerusalem, Israel.
jack.shainsky@usa.net

[d_wzrdv_z]

Try using square brakets for tables and then try apostrophes, remember to delimit them, in a CString, and pass the string in the method; I have had awful trouble with SQL statements because I think the parser in MFC DAO is rather particular. Just what I'd first try,
HTH

Hi,

another way to do this:

float fDisc = 0.25;
CString strSQL = _T("");

rsProducts.m_strFilter = "[OrderDetails]";
rsProducts.m_strSort = "[Products]";
rsProducts.requery();

strSQL.Format("[Discount] >= %f", fDisc);
if( rsProducts.FindFirst( strSQL ) != 0 )
{
// do something
...

HTH,

DPL

if(