question

[Guest]

I have a little quetion.
I know how to make a query based on another query.
But if i write the first query in Vba, how i can write the second quey
always in Vba based on the first?
Is it possiblr or not?

 

[Rick B]

write in vba?

you mean sql?

 

[Guest]

yes i mean sql in vba
i done it and is working for the first query.
But i don't know how to do it for the second query based on the first

 

[Rick B]

I'm pretty sure to create a query and base it off an existing query, that
the existing query would need to be a saved, named query.

Someone else might have a better answer for you though.

 

[Guest]

Thanks for the reply
Yes i know how to do it if it's a saved named query.
I'd like to know if it's possible if is not saved
Hope someone will help me

 

[John Spencer (MVP)]

Well, you can nest the first query as a sub-query in the second query.

Without a more thorough explanation, the idea is something like the following

SELECT A.FieldA, S.FieldTwo
FROM [SELECT T.FieldA, T.FieldB
FROM SomeTable as T
WHERE T.FieldC = 98]. as A
INNER JOIN SecondTable as S
ON A.FieldB = S.FieldX
WHERE S.FieldTwo > 83

Note the square brackets around the subquery in the FROM clause and the period
at the end. This is the convention that Access uses for this query. The
limitations are that you cannot have any square brackets inside the sub-query.
That means no field or table names with spaces or special characters in them; no
reserved words in the sub-query; no parameters (although that is probably not
likely if you are building the query string in VBA).

The basic idea would be to copy your existing query and paste it into the new query.

If you try this and can't get it working, you might try posting the sql of your
first query and the sql of your second query and perhaps someone can post the
solution for you.