(Developing for ACCESS 2003)
Attempt 1 using
QRY1
SELECT [a].aCID, Count([a].Name) AS CNT
FROM [Select Distinct cid as aCID, Name, Status, Rank from tbl ]. AS a
WHERE [a].Status = yes and [a].Rank = "x"
GROUP BY [a].aCID
ORDER BY [a].aCID;
nCount = Dlookup("[xCID]","QRY1","xcid = '" & vrCIDval & "'")
nCount equates properly but a move to next record (next CID) generates a VBA runtime Error stating that the previous process is still open???
Is Dlookup leaving objects open?
QRY2
PARAMETERS pcid Text ( 255 );
SELECT Count(tbl.CID) AS Cnt
FROM tbl
WHERE tbl.Status=Yes and cid = pcid and Rank = "x";
using query.execute ... this states execute doesn't support SELECT statements
other then iterating via an ADO and counting the records the long way how do I get the value of Count from either of these Queries in VBA?
Tbl ... ID CID Name Rank Description
01 123 ABC x blah
02 123 ABS y blah
03 124 ADB x blah
04 124 ADX x blah
so expecting CID 123 to return 1 and CID 124 to return 2 ... the Queries work but haven't been able to get the values in VBA from the queries