SQL Sum not working

[harrisw]

I have the code below in a routine

Dim dBase As Database
Dim rsSet As Recordset
Dim count As Integer
Dim SQL As String
Dim jobSQL As Currency
Dim rsJob As Recordset

Set dBase = CurrentDb

SQL = "SELECT count(recordid) FROM tblHistory WHERE tblhistory![recordid] = '" _
& Forms!frmMain.Form!ID & "' and [history type]= '" & "Complaint" & "'"

jobSQL = "SELECT sum(amounttobecharged) FROM tbljobs WHERE tbljobs![mainjobid] = '" _
& Forms!frmMain.Form!ID & "'"

Set rsSet = dBase.OpenRecordset(SQL)
Set rsJob = dBase.OpenRecordset(jobSQL)

Form_frmMain.txtAnyComplaints = rsSet.Fields(0)
Form_frmMain.Turnover = rsJob.Fields(0)

I get a type mismatch error on line

jobSQL = "SELECT sum(amounttobecharged) FROM tbljobs WHERE tbljobs![mainjobid] = '" _
& Forms!frmMain.Form!ID & "'"

Any ideas?

 

[harrisw]

reply

Got it it was the ' around the field. It was turning it into a string and not leaving it numeric.