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!
DeliriousAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.