sub query syntax

[tsmith]

I have a pass-thru query that retrieves data from our DB2 db on our AS/400. When I run this interactively, it works and returns the data I asked for.

I have a local (Access? frontend?) query that uses the pass-thru query as input, to append to a local table on my PC. This too works.

But my parameters are variable, and I've been trying to build my queries in VB and execute them. I started by following Access's lead, which looked wrong, even to me. (More beginner than guru.)

Here's what I have now:

I created this:

' compose SQL string 1 for the back end (BE) - DB2
'
strSQLselect1 = _
"SELECT distinct " & _
"CXLIB.UT420AP.UTCSID, " & _
"CXLIB.UT420AP.UTLCID, " & _
"CXLIB.UT420AP.UTRCLS, " & _
"CXLIB.UT420AP.UTSVC, " & _
"CXLIB.UT420AP.UTPEYY, " & _
"CXLIB.UT420AP.UTPEMM, " & _
"CXLIB.UT420AP.UTAGE, " & _
"CXLIB.UT420AP.UTTTYP, " & _
"CXLIB.UT420AP.UTTDSC, " & _
"CXLIB.UT420AP.UTTAMT, " & _
"CXLIB.UT420AP.UTUNPD "

strSQLfrom1 = _
"FROM CXLIB.UT420AP "

strSQLwhere1 = _
"WHERE " & _
"((CXLIB.UT420AP.UTAGE='" & strAgeGroup & "') AND " & _
"(((CXLIB.UT420AP.UTPEMM)=" & int1stMM & ") AND ((CXLIB.UT420AP.UTPEYY)="
& int1stYY & ")) Or " & _
"(((CXLIB.UT420AP.UTPEMM)=" & int2ndMM & ") AND ((CXLIB.UT420AP.UTPEYY)="
& int2ndYY & "))) "

strSQLorderby1 = _
"ORDER BY " & _
"CXLIB.UT420AP.UTRCLS, " & _
"CXLIB.UT420AP.UTSVC, " & _
"CXLIB.UT420AP.UTPEYY, " & _
"CXLIB.UT420AP.UTPEMM, " & _
"CXLIB.UT420AP.UTTTYP, " & _
"CXLIB.UT420AP.UTTDSC; "

strSQLstatement1 = _
strSQLselect1 & _
strSQLfrom1 & _
strSQLwhere1 & _
strSQLorderby1


' compose SQL string 2 for the front end (FE) - MS Access
'
strSQLinsert2 = _
"INSERT INTO tblAcctsRecAging_Details " & _
"( CustID, " & _
"LocID, " & _
"CustClass, " & _
"Serv, " & _
"PeriodYear, " & _
"PeriodMonth, " & _
"AgeCode, " & _
"ChgType, " & _
"ChgDesc, " & _
strWhichAmtBilled & "AmtBilled, " & _
strWhichAmtUnpaid & "AmtUnpaid ) "

strSQLselect2 = _
"SELECT " & _
"UTCSID, " & _
"UTLCID, " & _
"UTRCLS, " & _
"UTSVC, " & _
"UTPEYY, " & _
"UTPEMM, " & _
"UTAGE, " & _
"UTTTYP, " & _
"UTTDSC, " & _
"UTTAMT, " & _
"UTUNPD "

strSQLfrom2 = _
"FROM CXLIB.UT420AP "

strSQLwhere2 = _
"WHERE " & _
"tblAcctsRecAging_Details RIGHT JOIN in (" & strSQLstatement1 & ") ON "
& _
"tblAcctsRecAging_Details.LocID = [CXLIB.UT420AP.UTLCID]; "

strSQLstatement2 = _
strSQLinsert2 & _
strSQLselect2 & _
strSQLfrom2 & _
strSQLwhere2

I'm still geting an error, so I obviously do not understand the nuances of sub (nested) queries.

Can anyone please help? Thanks in advance.

Tom

[xordevoreaux]

On this area of the string:

strWhichAmtBilled & "AmtBilled, " & _
strWhichAmtUnpaid & "AmtUnpaid )

what if you put a space before the quotes:

strWhichAmtBilled & " AmtBilled, " & _
strWhichAmtUnpaid & " AmtUnpaid )

because otherwise your strWhichAmtBilled string and "amtBilled" would run together in the final query string, right?

[tsmith]

Thanks. I did finally find out what the problem was. I needed my nested query to be a pass-thru query for Access. (Specific to my DB2 db on my 400.)

Trying to do it, the WAY I was trying to do it, was...WRONG.

I finally got it working by building a QueryDef for both my local (front end) query and pass-thru (back end) queries.

And it works...just Fiiiiine.

But I do thank you for the suggestion.

Tom