• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 502
  • Last Modified:

Reserved Error 3146

I am getting a  Reserved Error 3146.  I think the following SQL is causing the error, but I don't see anything wrong.  Could someone take a look and let me know if you can see anything in the code that would be causing this error.  It is SQL in a module.

If strReportType = "GENERIC" Then

strSQL = "TRANSFORM Sum(qyr_CATSDB.CATSHOURS) AS SumOfCATSHOURS " _

& " SELECT WBS_LIST.PROJECT_NAME, WBS_LIST.PROGRAM, qyr_CATSDB.CHARGE_CODE, WBS_LIST.DESC_1,WBS_LIST.CAM, Left([TASKLEVEL],1) & [TASKCOMPONENT] AS RTC, IIf(Len([SKOSTL])=7,Right([SKOSTL],2),[SKOSTL]) AS RESOURCE, IIf(Len([SKOSTL])=7,Left([SKOSTL],5),[SKOSTL]) AS OAT, " & dblHours & " AS HOURS, P4S_WRITE_USER_Z_R3_PA0001_TBL.ENDDA, P4S_WRITE_USER_Z_R3_PA0001_TBL.SNAME AS [EMPLOYEE NAME], '" & strStartDate & "' AS [START DATE], '" & strEndDate & "' AS [END DATE] " _
& " FROM (qyr_CATSDB INNER JOIN WBS_LIST ON qyr_CATSDB.CHARGE_CODE = WBS_LIST.WBS) INNER JOIN P4S_WRITE_USER_Z_R3_PA0001_TBL ON qyr_CATSDB.PERNR = P4S_WRITE_USER_Z_R3_PA0001_TBL.PERNR " _
& " WHERE (((P4S_WRITE_USER_Z_R3_PA0001_TBL.ENDDA)=#12/31/9999#) AND ((qyr_CATSDB.ERSDA) Between #" & strStartDate & "# And #" & strEndDate
& "#) AND ((WBS_LIST.PROJECT_NAME)='" & strProjectName & "') AND ((qyr_CATSDB.AWART)='01ST' Or (qyr_CATSDB.AWART)='02OT' Or (qyr_CATSDB.AWART)='03DT') AND ((qyr_CATSDB.STATUS)='20' Or (qyr_CATSDB.STATUS)='30') AND ((WBS_LIST.CAM)='strCAM')) " _
& " GROUP BY WBS_LIST.PROJECT_NAME, WBS_LIST.PROGRAM, qyr_CATSDB.CHARGE_CODE,WBS_LIST.CAM, WBS_LIST.DESC_1, Left([TASKLEVEL],1) & [TASKCOMPONENT], IIf(Len([SKOSTL])=7,Right([SKOSTL],2),[SKOSTL]), IIf(Len([SKOSTL])=7,Left([SKOSTL],5),[SKOSTL]), " & dblHours & ", P4S_WRITE_USER_Z_R3_PA0001_TBL.ENDDA, P4S_WRITE_USER_Z_R3_PA0001_TBL.SNAME, '" & strStartDate & "', '" & strEndDate & "' " _
& " ORDER BY qyr_CATSDB.CHARGE_CODE " _
& " PIVOT qyr_CATSDB.AWART In ('01ST','02OT','03DT');"

ElseIf strReportType = "PROJECT" Then

strSQL = "TRANSFORM Sum(qyr_CATSDB.CATSHOURS) AS SumOfCATSHOURS " _
& " SELECT WBS_LIST.PROJECT_NAME, WBS_LIST.PROGRAM, qyr_CATSDB.CHARGE_CODE, WBS_LIST.CAM, WBS_LIST.DESC_1, Left([TASKLEVEL],1) & [TASKCOMPONENT] AS RTC, IIf(Len([SKOSTL])=7,Right([SKOSTL],2),[SKOSTL]) AS RESOURCE, IIf(Len([SKOSTL])=7,Left([SKOSTL],5),[SKOSTL]) AS OAT, " & dblHours & " AS HOURS, P4S_WRITE_USER_Z_R3_PA0001_TBL.ENDDA, P4S_WRITE_USER_Z_R3_PA0001_TBL.SNAME AS [EMPLOYEE NAME], '" & strStartDate & "' AS [START DATE], '" & strEndDate & "' AS [END DATE] " _
& " FROM WBS_LIST, qyr_CATSDB INNER JOIN P4S_WRITE_USER_Z_R3_PA0001_TBL ON qyr_CATSDB.PERNR = P4S_WRITE_USER_Z_R3_PA0001_TBL.PERNR " _
& " WHERE (((P4S_WRITE_USER_Z_R3_PA0001_TBL.ENDDA)=#12/31/9999#) AND ((qyr_CATSDB.ERSDA) Between #" & strStartDate & "# And #" & strEndDate & "#) AND ((WBS_LIST.PROJECT_NAME)='" & strProjectName & "') AND ((qyr_CATSDB.AWART)='01ST' Or (qyr_CATSDB.AWART)='02OT' Or (qyr_CATSDB.AWART)='03DT') AND ((qyr_CATSDB.STATUS)='20' Or (qyr_CATSDB.STATUS)='30')AND(WBS_LIST.CAM)='strCAM') AND ((qyr_CATSDB.WBS_ELEMENT) Like [WBS_LIST].[WBS] & '*')) " _
& " GROUP BY WBS_LIST.PROJECT_NAME, WBS_LIST.PROGRAM, qyr_CATSDB.CHARGE_CODE, WBS_LIST.CAM, WBS_LIST.DESC_1, Left([TASKLEVEL],1) & [TASKCOMPONENT], IIf(Len([SKOSTL])=7,Right([SKOSTL],2),[SKOSTL]), IIf(Len([SKOSTL])=7,Left([SKOSTL],5),[SKOSTL]), " & dblHours & ", P4S_WRITE_USER_Z_R3_PA0001_TBL.ENDDA, P4S_WRITE_USER_Z_R3_PA0001_TBL.SNAME, '" & strStartDate & "', '" & strEndDate & "', qyr_CATSDB.WBS_ELEMENT " _
& " ORDER BY qyr_CATSDB.CHARGE_CODE " _
& " PIVOT qyr_CATSDB.AWART In ('01ST','02OT','03DT');"

End If


Thanks for your help!
0
Delirious
Asked:
Delirious
1 Solution
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
What are you doing with the strSQL variable? Are you opening up a recordset, or using this to fill a query, etc etc?

Where exactly do you get the error? I'm assuming it's somewhere after your If - ElseIf - End If structure.

0
 
DeliriousAuthor Commented:
Well, I figured it out.

I never would have without are comment though, so I am giving you the points.

When typing up an answer I deceided to follow the strSQL variable from begining to end.  The query is being refrenced by a report at the end.  There seemed to be a problem with what I was trying to do to the report.  The error was actualy being generated right here:

DoCmd.OpenReport Me.txtReportType, acViewPreview, , "[CAM]='" & Me![txtCAM] & "'"
DoCmd.SendObject acSendReport, "rpt_Generic_Report_CAM_GENERIC", "SnapshotFormat(*.snp)", Nz(Me.txtCAM.Column(1), Me.txtCAM), , , "ACTUALS", , True
DoCmd.Close acReport, "rpt_Generic_Report_CAM_GENERIC"

Why was I Opening and Closing the report?  I took that code out and everything workd perfectly.

Thanks for the time.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now