• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 498
  • 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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

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