help with recordset creation

[Peter Bailey]

I have tested two queries in the query grid and want to create a dynamic
string in vba and then create one recordset that returns the same but cannot
quite get my head around one sql string that will do the same. Q2 just
groups and does a count on Q1.

Many regards in advance

Q1 :

SELECT Qrygetyearsofbookings_1.[Year From],
Count(Qrygetyearsofbookings_1.[DOSM-DSName]) AS [CountOfDOSM-DSName]
FROM Qrygetyearsofbookings AS Qrygetyearsofbookings_1
GROUP BY Qrygetyearsofbookings_1.[Year From];
Q2:

SELECT Format([From],"yyyy") AS [Year From], DOSMBK.[DOSM-DSName]
FROM DOSMBK
GROUP BY Format([From],"yyyy"), DOSMBK.[DOSM-DSName]
HAVING (((DOSMBK.[DOSM-DSName]) Like "M9S" Or
(DOSMBK.[DOSM-DSName])="M9SA"));

[Pieter Linden]

is there a problem with opening a query off a stored querydef? Going
that way is probably the easiest... failing that, build your query
with the QBE grid, copy the SQL and paste it into a string variable,
and replace all double quotes with singles (using the Replace
function). Then you can open a recordset by opening a recordset that
has a SQL string as a source.

[Peter Bailey]

I did that late last night in the end I just thought it was more elegant not
to have to use queries in the database as the previous developer has 1000 of
them and so I preferred to do them dynamically. I never knew that I could
use a recordset as the source for a recordset.

what is the syntax in this case

Your advice is not wasted thank you very much Pieter.
"Pieter Linden" <pi********@hotmail.com> wrote in message
news:bf**************************@posting.google.c om...

is there a problem with opening a query off a stored querydef? Going
that way is probably the easiest... failing that, build your query
with the QBE grid, copy the SQL and paste it into a string variable,
and replace all double quotes with singles (using the Replace
function). Then you can open a recordset by opening a recordset that
has a SQL string as a source.