I'm running a report via VBA. Upon opening the report, I receive the following error: Run-Time Error '13; Type mismatch. When debugging it stops at Line 2 (Estimate) (see attached code). Can someone please help me in trying to fix this problem?
Thanks in advance! :-{}============================================
tblAcct0973.intProjectCode, tblAcct0973.intTaskCode"[/code]
Thanks in advance! :-{}============================================
Code:
Private Sub GetReportTotals()
Code:
On Error GoTo Err_0973Report
Code:
Code:
Dim rst As DAO.Recordset
Code:
Dim rst2 As DAO.Recordset
Code:
Dim strSQL As String
Code:
Dim strSQL2 As String
Code:
Code:
'Estimate
Code:
strSQL = "TRANSFORM Sum(Round([curEstimate])) [code]AS Estimate"
Code:
[COLOR=red]strSQL = strSQL & " SELECT Format([tblProject.intProjectCode],'0000000') & " - " & Format([tblProject.intTaskCode],'000') AS ProjectTask" [/color]
Code:
strSQL = strSQL & " FROM tblProject INNER JOIN (tblFYMonth INNER JOIN tblAcct0973 ON tblFYMonth.intFYMonth = tblAcct0973.intFYMonth) ON (tblProject.intTaskCode = tblAcct0973.intTaskCode) AND (tblProject.intProjectCode = tblAcct0973.intProjectCode)"
Code:
strSQL = strSQL & " GROUP BY Format([tblProject.intProjectCode],'0000000') & " - " & Format([tblProject.intTaskCode],'000'),
Code:
strSQL = strSQL & " PIVOT tblFYMonth.txtMonthName In ('OCT', 'NOV', 'DEC', 'JAN', 'FEB', 'MAR', 'APR', 'MAY', 'JUN', 'JUL', 'AUG', 'SEP');"
Code:
Code:
'Used
Code:
strSQL2 = "TRANSFORM Sum(Round([curAmount])) AS SumTotal"
Code:
strSQL2 = strSQL2 & " SELECT Format([tblProject.intProjectCode],'0000000') & " - " & Format([tblProject.intTaskCode],'000') AS [Project/Task], tblExpense.intObjectGroup, Sum(Round([curAmount])) AS TotalOfcurAmount"
Code:
strSQL2 = strSQL2 & " FROM tblProject INNER JOIN (tblObjectGroup INNER JOIN (tblFYMonth INNER JOIN tblExpense ON tblFYMonth.intFYMonth = tblExpense.intFYMonth) ON tblObjectGroup.intObjectGroup = tblExpense.intObjectGroup) ON (tblProject.intTaskCode = tblExpense.intTaskCode) AND (tblProject.intProjectCode = tblExpense.intProjectCode)"
Code:
strSQL2 = strSQL2 & " WHERE (((tblExpense.intObjectGroup)=52060300) AND ((tblExpense.intProjectCode) Like '769*') AND ((Left$([intbranchcode],2))=60))"
Code:
strSQL2 = strSQL2 & " GROUP BY Format([tblProject.intProjectCode],'0000000') & " - " & Format([tblProject.intTaskCode],'000'), tblExpense.intObjectGroup, tblExpense.intProjectCode, tblExpense.intTaskCode"
Code:
strSQL2 = strSQL2 & " PIVOT tblFYMonth.txtMonthName In ('OCT', 'NOV', 'DEC', 'JAN', 'FEB', 'MAR', 'APR', 'MAY', 'JUN', 'JUL', 'AUG', 'SEP');"
Code:
Code:
Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot, dbReadOnly)
Code:
Set rst2 = CurrentDb.OpenRecordset(strSQL2, dbOpenSnapshot, dbReadOnly)
Code:
Code:
'Estimate
Code:
Do While Not rst.EOF
Code:
Me.ProjectTaskEstimate = rst("Project/Task")
Code:
Me.OctEstimate = rst("Oct")
Code:
Me.NovEstimate = rst("Nov")
Code:
Me.DecEstimate = rst("Dec")
Code:
Me.JanEstimate = rst("Jan")
Code:
Me.FebEstimate = rst("Feb")
Code:
Me.MarEstimate = rst("Mar")
Code:
Me.AprEstimate = rst("Apr")
Code:
Me.MayEstimate = rst("May")
Code:
Me.JunEstimate = rst("Jun")
Code:
Me.JulEstimate = rst("Jul")
Code:
Me.AugEstimate = rst("Aug")
Code:
Me.SepEstimate = rst("Sep")
Code:
rst.MoveNext
Code:
Loop
Code:
Code:
'Used
Code:
Do While Not rst2.EOF
Code:
Me.ProjectTaskUsed = rst2("Project/Task")
Code:
Me.OctUsed = rst2("Oct")
Code:
Me.NovUsed = rst2("Nov")
Code:
Me.DecUsed = rst2("Dec")
Code:
Me.JanUsed = rst2("Jan")
Code:
Me.FebUsed = rst2("Feb")
Code:
Me.MarUsed = rst2("Mar")
Code:
Me.AprUsed = rst2("Apr")
Code:
Me.MayUsed = rst2("May")
Code:
Me.JunUsed = rst2("Jun")
Code:
Me.JulUsed = rst2("Jul")
Code:
Me.AugUsed = rst2("Aug")
Code:
Me.SepUsed = rst2("Sep")
Code:
rst2.MoveNext
Code:
Loop
Code:
Code:
Exit_0973Report:
Code:
rst2.Close
Code:
rst.Close
Code:
Exit Sub
Code:
Err_0973Report:
Code:
MsgBox Err.Description
Code:
Resume Exit_0973Report
Code:
End Sub