Run-Time Error '13: Type mismatch

[dnayana]

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! :-{}============================================

Private Sub GetReportTotals()

On Error GoTo Err_0973Report

Dim rst As DAO.Recordset

Dim rst2 As DAO.Recordset

Dim strSQL As String

Dim strSQL2 As String

'Estimate

strSQL = "TRANSFORM Sum(Round([curEstimate])) [code]AS Estimate"

[COLOR=red]strSQL = strSQL & " SELECT Format([tblProject.intProjectCode],'0000000') & " - " & Format([tblProject.intTaskCode],'000') AS ProjectTask" [/color]

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)"

strSQL = strSQL & " GROUP BY Format([tblProject.intProjectCode],'0000000') & " - " & Format([tblProject.intTaskCode],'000'),

tblAcct0973.intProjectCode, tblAcct0973.intTaskCode"[/code]

strSQL = strSQL & " PIVOT tblFYMonth.txtMonthName In ('OCT', 'NOV', 'DEC', 'JAN', 'FEB', 'MAR', 'APR', 'MAY', 'JUN', 'JUL', 'AUG', 'SEP');"

'Used

strSQL2 = "TRANSFORM Sum(Round([curAmount])) AS SumTotal"

strSQL2 = strSQL2 & " SELECT Format([tblProject.intProjectCode],'0000000') & " - " & Format([tblProject.intTaskCode],'000') AS [Project/Task], tblExpense.intObjectGroup, Sum(Round([curAmount])) AS TotalOfcurAmount"

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)"

strSQL2 = strSQL2 & " WHERE (((tblExpense.intObjectGroup)=52060300) AND ((tblExpense.intProjectCode) Like '769*') AND ((Left$([intbranchcode],2))=60))"

strSQL2 = strSQL2 & " GROUP BY Format([tblProject.intProjectCode],'0000000') & " - " & Format([tblProject.intTaskCode],'000'), tblExpense.intObjectGroup, tblExpense.intProjectCode, tblExpense.intTaskCode"

strSQL2 = strSQL2 & " PIVOT tblFYMonth.txtMonthName In ('OCT', 'NOV', 'DEC', 'JAN', 'FEB', 'MAR', 'APR', 'MAY', 'JUN', 'JUL', 'AUG', 'SEP');"

Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot, dbReadOnly)

Set rst2 = CurrentDb.OpenRecordset(strSQL2, dbOpenSnapshot, dbReadOnly)

'Estimate

Do While Not rst.EOF

    Me.ProjectTaskEstimate = rst("Project/Task")

    Me.OctEstimate = rst("Oct")

    Me.NovEstimate = rst("Nov")

    Me.DecEstimate = rst("Dec")

    Me.JanEstimate = rst("Jan")

    Me.FebEstimate = rst("Feb")

    Me.MarEstimate = rst("Mar")

    Me.AprEstimate = rst("Apr")

    Me.MayEstimate = rst("May")

    Me.JunEstimate = rst("Jun")

    Me.JulEstimate = rst("Jul")

    Me.AugEstimate = rst("Aug")

    Me.SepEstimate = rst("Sep")

    rst.MoveNext

Loop

'Used

Do While Not rst2.EOF

    Me.ProjectTaskUsed = rst2("Project/Task")

    Me.OctUsed = rst2("Oct")

    Me.NovUsed = rst2("Nov")

    Me.DecUsed = rst2("Dec")

    Me.JanUsed = rst2("Jan")

    Me.FebUsed = rst2("Feb")

    Me.MarUsed = rst2("Mar")

    Me.AprUsed = rst2("Apr")

    Me.MayUsed = rst2("May")

    Me.JunUsed = rst2("Jun")

    Me.JulUsed = rst2("Jul")

    Me.AugUsed = rst2("Aug")

    Me.SepUsed = rst2("Sep")

    rst2.MoveNext

Loop

Exit_0973Report:

    rst2.Close

    rst.Close

    Exit Sub

Err_0973Report:

    MsgBox Err.Description

    Resume Exit_0973Report

End Sub

 

[PaulBricker]

What you are saying is it stops right before the first strSQL statement. On the commented 'Estimate line. If that's the case, what version of Access are you using. It has to be in the Dim statements. You can get that error if you don't declare the Recordsets
DAO.Recordset
but you are doing that. Try taking out the DAO. library reference and see if it runs.

Paul

 

[dnayana]

Hi Paul!

Thanks for responding.

I'm using Access 2000. It is stopping at the second line of the 'Estimate section. The code that is in red, is where my error is. I tried your suggestion of taking out DAO, but that still didn't work. I have the slightest clue as to why I'm receiving the error because I copied/paste the SQL statement from the SQL View of the query (making the necessary modifications) into my mod.

Nicole :-{}

 

[PaulBricker]

Nicole, sorry I missed that RED line.
Change the " - " to ' - ' in all your strings. That should clear it up.

Paul

 

[dnayana]

Paul,

Thank you. That helped a bunch.

Have a safe and Happy Thanksgiving!!

Nicole :-{}