subquery in VBA

[geebee]

hi,

i have 2 saved queries. sub and main. the main contains the sub within it.
i would like to combine the 2 in VBA so that I can use SQL instead of
referencing the saved subquery name. here are the 2 queries:

saved as sub
SELECT DISTINCT MTL.SD, SITS.Sit1, MTL.[TMLN] AS [TL],
StrConv(Trim(Right([FN],Len([FN])-InStr(1,[FN],','))) & ' ' &
Trim(Left([FN],InStr(1,[FN],',')-1)),3) AS Agent, MTL.Acct AS [ACCT#],
MTL.OT, MTL.TT, MTL.HT, MTL.WT, IIf([TT]+[HT]+[WT] Between 2000 And
2999,'2000 - 2999',IIf([TT]+[HT]+[WT] Between 3000 And 3999,'3000 -
3999',IIf([TT]+[HT]+[WT]>=4000,'>=4000'))) AS [HANT], MTL.DI, table2!FN & ' '
& table2!LN AS MGR, MTL.uNEW, MTL.AP#, table2.EN
FROM (MTL LEFT JOIN SITS ON MTL.ACDD=SITS.ACDD) LEFT JOIN (table1 LEFT JOIN
table2 ON table1.MEN=table2.EN) ON MTL.uNEW=table1.EN
WHERE ((([TT]+[HT]+[WT])>=2000) AND ((table1.LVL)=0 Or (table1.LVL) Is Null))
ORDER BY MTL.[TMLN] DESC , IIf([TT]+[HT]+[WT] Between 2000 And 2999,'2000 -
2999',IIf([TT]+[HT]+[WT] Between 3000 And 3999,'3000 -
3999',IIf([TT]+[HT]+[WT]>=4000,'>=4000'))) DESC;

saved as main
SELECT sub.col1...
FROM (sub LEFT JOIN table1 ON sub.EN=table1.EN) LEFT JOIN table2 ON
table1.MEN=table2.EN
WHERE (((table1.LEVEL)=0 Or (table1.LEVEL) Is Null));

can someone tell me how i can write a main query referencing the sub.. in VBA?

thanks in advance,
geebee

[John Spencer]

SELECT sub.col1...
FROM (
(SELECT DISTINCT MTL.SD, SITS.Sit1, MTL.[TMLN] AS [TL],

StrConv(Trim(Right([FN],Len([FN])-InStr(1,[FN],','))) & ' ' &
Trim(Left([FN],InStr(1,[FN],',')-1)),3) AS Agent, MTL.Acct AS [ACCT#]
, MTL.OT, MTL.TT, MTL.HT, MTL.WT

, IIf([TT]+[HT]+[WT] Between 2000 And 2999,'2000 - 2999'
,IIf([TT]+[HT]+[WT] Between 3000 And 3999,'3000 - 3999'

,IIf([TT]+[HT]+[WT]>=4000,'>=4000'))) AS [HANT]

, MTL.DI, table2!FN & ' ' & table2!LN AS MGR, MTL.uNEW, MTL.[AP#]

, table2.EN
FROM (MTL LEFT JOIN SITS ON MTL.ACDD=SITS.ACDD)
LEFT JOIN (table1 LEFT JOIN
table2 ON table1.MEN=table2.EN) ON MTL.uNEW=table1.EN
WHERE ((([TT]+[HT]+[WT])>=2000) AND ((table1.LVL)=0

Or (table1.LVL) Is Null))) As Sub

LEFT JOIN table1 ON sub.EN=table1.EN) LEFT JOIN table2 ON
table1.MEN=table2.EN
WHERE (((table1.LEVEL)=0 Or (table1.LEVEL) Is Null));

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

[John Spencer]

I forgot that this embedded subquery might fail because you have a field name
that contains the character #. That requires you use square brackets around
the fields name and the alias which will usually cause Access to generate an
error.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

[Dirk Goldgar]

"John Spencer" <spe...@chpdm.edu> wrote in message
news:elJmrRXb...@TK2MSFTNGP04.phx.gbl...

>I forgot that this embedded subquery might fail because you have a field
>name that contains the character #. That requires you use square brackets
>around the fields name and the alias which will usually cause Access to
>generate an error.

John -

If geebee is building the SQL statement in code, and never opens it in the
query designer, I don't think that will be a problem. The problem occurs
because the query designer changes the SQL to replace the parentheses around
the derived table with square brackets, which then interfere with the
parsing of square brackets around field names. That doesn't happen if the
query is never opened in the query designer.

--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)

[John Spencer]

I believe that is correct; however, I felt the need to point out the
possibility in case geebee ever decided to save the SQL as a query or if the
query did fail.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County