CODE
SELECT #PreliminaryTempA.GrantID, VML.Extension, VU.FirstName, VU.LastName, #PreliminaryTempA.Dept,
#PreliminaryTempA.CFDA, #PreliminaryTempA.ProgramTitle,
'Period Ended 06/30/' + Right((SELECT DISTINCT FiscalYear FROM TimePeriods
WHERE MonthBeg ={ts '2013-07-01 00:00:00'}),2) AS FY, SumOfClaims, SumOfExpEstimates, MajorProgDes,
(SELECT CASE WHEN MajorProgDes=' (1)' THEN SumOfExpEstimates Else: 0 End) AS MajorTotal,
(SELECT CASE WHEN MajorProgDes=' (1)' THEN SumOfClaims Else: 0 End)
AS MajorTotal2, CFDAClmTot, CFDAExpEstTot, CFDAClmTot AS LargerTot
FROM #PreliminaryTempA LEFT JOIN #PreliminaryTempB
ON #PreliminaryTempA.CFDA = #PreliminaryTempB.CFDA INNER JOIN ViewMasterLedger VML
ON VML.GrantID = #PreliminaryTempA .GrantID LEFT JOIN ViewUsers VU ON VML.UserNo = VU.UserNo
WHERE 1 = 1 AND #PreliminaryTempA.Dept = '041'
I'm getting Invalid Syntax near '=' on lines 5 and 6. Any ideas?
Here's the code within VBA. I took it out and put it in Management Studio to see a more specific error description.
CODE
SQL " SELECT #PreliminaryTempA.GrantID, VML.Extension, VU.FirstName, VU.LastName, #PreliminaryTempA.Dept,"
SQL " #PreliminaryTempA.CFDA, #PreliminaryTempA.ProgramTitle, "
If gsReportNo = 3230 Then SQL " 'Period Ended 03/31/' + Right((SELECT DISTINCT FiscalYear"
If gsReportNo = 3235 Then SQL " 'Period Ended 06/30/' + Right((SELECT DISTINCT FiscalYear"
SQL " FROM TimePeriods"
SQL " WHERE MonthBeg =?),2) AS FY,"
SQL " SumOfClaims,"
SQL " SumOfExpEstimates, MajorProgDes, "
SQL " (SELECT CASE WHEN MajorProgDes=' (1)'"
SQL " THEN SumOfExpEstimates"
SQL " Else: 0 End) AS MajorTotal,"
SQL " (SELECT CASE WHEN MajorProgDes=' (1)'"
SQL " THEN SumOfClaims"
SQL " Else: 0 End) AS MajorTotal2,"
SQL " CFDAClmTot, CFDAExpEstTot,"
' At interim the larger total between claims and estimates should be estimates. This piece of code
' selects the larger of the two, incase estimates are less than claims
If gsReportNo = 3230 Then SQL " (SELECT CASE WHEN ISNULL(CFDAExpEstTot,0) > ISNULL(CFDAClmTot,0) THEN CFDAExpEstTot"
If gsReportNo = 3230 Then SQL " WHEN ISNULL(CFDAExpEstTot,0) = ISNULL(CFDAClmTot,0) THEN CFDAExpEStTot"
If gsReportNo = 3230 Then SQL " Else: CFDAClmTot"
If gsReportNo = 3230 Then SQL " END) AS LargerTot"
' At Year-end, we care more about actuals than we do about estimates
If gsReportNo = 3235 Then SQL " CFDAClmTot AS LargerTot"
SQL " FROM #PreliminaryTempA"
SQL " LEFT JOIN #PreliminaryTempB"
SQL " ON #PreliminaryTempA.CFDA = #PreliminaryTempB.CFDA"
SQL " INNER JOIN ViewMasterLedger VML"
SQL " ON VML.GrantID = #PreliminaryTempA .GrantID"
SQL " LEFT JOIN ViewUsers VU"
SQL " ON VML.UserNo = VU.UserNo"
SQL " WHERE 1 = 1"
' Additional Optional criteria
Dim YesNo As String
If mFrm!dMajor = "Y" Then SQL " AND MajorProgDes = ' (1)'"
'If Not Nz(mFrm!dMajor, "") = "" Then SQL " AND MajorProgDes = ? "
If Not Nz(mFrm!dCFDA, "") = "" Then SQL " AND #PreliminaryTempA.CFDA = ?"
If Not Nz(mFrm!dDept, "") = "" Then SQL " AND #PreliminaryTempA.Dept = ?"
' Continue Query
' none
' Parameters
SQLParam 1, mPosted
' Additional optional Parameter criteria
'If Not Nz(mFrm!dMajor, "") = "" Then
' SQLParam SQLParamCounter, mFrm.dMajor
' SQLParamCounter = SQLParamCounter + 1
' SQLParam 2, mFrm.dMajor
' End If
If Not Nz(mFrm!dCFDA, "") = "" Then
SQLParam SQLParamCounter, mFrm.dCFDA
SQLParamCounter = SQLParamCounter + 1
End If
If Not Nz(mFrm!dDept, "") = "" Then
SQLParam SQLParamCounter, mFrm.dDept
SQLParamCounter = SQLParamCounter + 1
End If
SetSQL gqdSrv, True
' Cache report data
CacheRecordSet gdbTemp, "ReportData", gqdSrv.OpenRecordset(dbOpenSnapshot)
Thank you in advance.