Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Run-Time Error '13: Type mismatch

Status
Not open for further replies.

dnayana

Programmer
Nov 14, 2002
53
US
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! :-{}============================================
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'),
tblAcct0973.intProjectCode, tblAcct0973.intTaskCode"[/code]
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
 
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
 
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 :-{}
 
Nicole, sorry I missed that RED line.
Change the " - " to ' - ' in all your strings. That should clear it up.

Paul
 
Paul,

Thank you. That helped a bunch.

Have a safe and Happy Thanksgiving!!

Nicole :-{}
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top