need pass thru query help

  • Thread starter Thread starter Tcs
  • Start date Start date
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...
 
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; )"
 
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.
 
I think you may have misunderstood my post. I wasn't
saying that it was incorrect to prefix the field names
with the table names (in fact it may be necessary for
your pass through), what I was saying was that the
variable strSQLstatement1 contained something entirely
different. strSQLstatement1 contained the entire sql
statement from your first query, so you were prefixing
each field name with the entire sql from your first
query. That is why, if you look at the error that you
posted, there are duplicate select statements at the
beginning.

I saw your later post as well, you may want to try adding
the table prefixes back to all of the field names to see
if it will work.

HTH, Ted Allen
-----Original Message-----
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.


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 I
retreive from our
qryAcctsRecAging_0010_Current_420_BE ON ((CXLIB.UT420AP.UTPEYY)
=4)) Or ((CXLIB.UT420AP.UTPEYY)
=4)))
strSQLstatement1 & ".UTLCID; )" distinct
SELECT distinct
Click to expand...

.
Click to expand...
 
Back
Top