need pass thru query help

T

Tcs

I have a local table into which I need to append data I retreive from our
AS/400. I initially developed both the local and pass thru queries which work
just fine together...with hardcoded parameters.

The local query code (which Access created):

INSERT INTO tblAcctsRecAging_Details
( CustID,
LocID,
CustClass,
Serv,
PeriodYear,
PeriodMonth,
AgeCode,
ChgType,
ChgDesc,
CurrentAmtBilled,
CurrentUnPaid )

SELECT
qryAcctsRecAging_0010_Current_420_BE.UTCSID,
qryAcctsRecAging_0010_Current_420_BE.UTLCID,
qryAcctsRecAging_0010_Current_420_BE.UTRCLS,
qryAcctsRecAging_0010_Current_420_BE.UTSVC,
qryAcctsRecAging_0010_Current_420_BE.UTPEYY,
qryAcctsRecAging_0010_Current_420_BE.UTPEMM,
qryAcctsRecAging_0010_Current_420_BE.UTAGE,
qryAcctsRecAging_0010_Current_420_BE.UTTTYP,
qryAcctsRecAging_0010_Current_420_BE.UTTDSC,
qryAcctsRecAging_0010_Current_420_BE.UTTAMT,
qryAcctsRecAging_0010_Current_420_BE.UTUNPD

FROM
tblAcctsRecAging_Details RIGHT JOIN qryAcctsRecAging_0010_Current_420_BE ON
tblAcctsRecAging_Details.LocID = qryAcctsRecAging_0010_Current_420_BE.UTLCID;

And the pass thru query (which I created):

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

FROM
CXLIB.UT420AP

WHERE
((CXLIB.UT420AP.UTAGE='C') AND
(((CXLIB.UT420AP.UTPEMM)=7) AND ((CXLIB.UT420AP.UTPEYY)=4)) Or
(((CXLIB.UT420AP.UTPEMM)=8) AND ((CXLIB.UT420AP.UTPEYY)=4)))

ORDER BY
CXLIB.UT420AP.UTRCLS,
CXLIB.UT420AP.UTSVC,
CXLIB.UT420AP.UTPEYY,
CXLIB.UT420AP.UTPEMM,
CXLIB.UT420AP.UTTTYP,
CXLIB.UT420AP.UTTDSC;

The problem is that my parameters are variable. So 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) - Access
'
strSQLinsert2 = _
"INSERT INTO tblAcctsRecAging_Details " & _
"( CustID, " & _
"LocID, " & _
"CustClass, " & _
"Serv, " & _
"PeriodYear, " & _
"PeriodMonth, " & _
"AgeCode, " & _
"ChgType, " & _
"ChgDesc, " & _
"CurrentAmtBilled, " & _
"CurrentUnPaid ) "

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

strSQLfrom2 = _
"FROM " & _
"tblAcctsRecAging_Details RIGHT JOIN " & strSQLstatement1 & " ON " & _
"tblAcctsRecAging_Details.LocID = " & strSQLstatement1 & ".UTLCID; )"

strSQLstatement2 = _
strSQLinsert2 & _
strSQLselect2 & _
strSQLfrom2

So NOW what's my problem? Access doesn't like this. I'm getting an error msg:

Error # 3075 was generated by MSAcess
Syntax error. in the query expression '(SELECT distinct 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'.

I don't understand what it doesn't like. Can/will anyone hazard a guess?

Access = 2000
OS = XP

Thanks a lot, in advance...
 
T

Ted Allen

Hi Tcs,

It looks to me like the error may be with the
construction of strSQLselect2. This uses
strSQLstatement1 as the prefix for each field name, but I
believe that strSQLstatement1 is the sql for your entire
first query. I think that if you replace that variable
with "CXLIB.UT420AP" you may get what you are looking for
(if I read it correctly).

HTH, Ted Allen
-----Original Message-----
I have a local table into which I need to append data I retreive from our
AS/400. I initially developed both the local and pass thru queries which work
just fine together...with hardcoded parameters.

The local query code (which Access created):

INSERT INTO tblAcctsRecAging_Details
( CustID,
LocID,
CustClass,
Serv,
PeriodYear,
PeriodMonth,
AgeCode,
ChgType,
ChgDesc,
CurrentAmtBilled,
CurrentUnPaid )

SELECT
qryAcctsRecAging_0010_Current_420_BE.UTCSID,
qryAcctsRecAging_0010_Current_420_BE.UTLCID,
qryAcctsRecAging_0010_Current_420_BE.UTRCLS,
qryAcctsRecAging_0010_Current_420_BE.UTSVC,
qryAcctsRecAging_0010_Current_420_BE.UTPEYY,
qryAcctsRecAging_0010_Current_420_BE.UTPEMM,
qryAcctsRecAging_0010_Current_420_BE.UTAGE,
qryAcctsRecAging_0010_Current_420_BE.UTTTYP,
qryAcctsRecAging_0010_Current_420_BE.UTTDSC,
qryAcctsRecAging_0010_Current_420_BE.UTTAMT,
qryAcctsRecAging_0010_Current_420_BE.UTUNPD

FROM
tblAcctsRecAging_Details RIGHT JOIN
Click to expand...
qryAcctsRecAging_0010_Current_420_BE ON
tblAcctsRecAging_Details.LocID = qryAcctsRecAging_0010_Current_420_BE.UTLCID;

And the pass thru query (which I created):

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

FROM
CXLIB.UT420AP

WHERE
((CXLIB.UT420AP.UTAGE='C') AND
(((CXLIB.UT420AP.UTPEMM)=7) AND ((CXLIB.UT420AP.UTPEYY) =4)) Or
(((CXLIB.UT420AP.UTPEMM)=8) AND ((CXLIB.UT420AP.UTPEYY) =4)))

ORDER BY
CXLIB.UT420AP.UTRCLS,
CXLIB.UT420AP.UTSVC,
CXLIB.UT420AP.UTPEYY,
CXLIB.UT420AP.UTPEMM,
CXLIB.UT420AP.UTTTYP,
CXLIB.UT420AP.UTTDSC;

The problem is that my parameters are variable. So 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) - Access
'
strSQLinsert2 = _
"INSERT INTO tblAcctsRecAging_Details " & _
"( CustID, " & _
"LocID, " & _
"CustClass, " & _
"Serv, " & _
"PeriodYear, " & _
"PeriodMonth, " & _
"AgeCode, " & _
"ChgType, " & _
"ChgDesc, " & _
"CurrentAmtBilled, " & _
"CurrentUnPaid ) "

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

strSQLfrom2 = _
"FROM " & _
"tblAcctsRecAging_Details RIGHT JOIN " & strSQLstatement1 & " ON " & _
"tblAcctsRecAging_Details.LocID = " &
Click to expand...
strSQLstatement1 & ".UTLCID; )"
 
T

Tcs

Well, since I haven't played with nested queries before, I had to use SOMETHING
as a guide, so I used Access's query...

I've since tried modifying it for what I believe the PROPER way to nest queries.
I'm still getting an error, but I [THINK I] am making progress.

Thanks.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

nested pass thru query problem 0

Top