Data type mismatch error message

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Windows XP Pro
Access 2002 (XP)

I am getting this error everytime I try to run this query. This query is
simply ouputing a total of all services along with each order number.

SELECT tblOrder.OrderNo,
IIf(Sum(RoundMoney([quantity]*[price]))>0,Sum(RoundMoney([quantity]*[price])),0) AS Service
FROM tblService RIGHT JOIN tblOrder ON tblService.OrderNo = tblOrder.OrderNo
GROUP BY tblOrder.OrderNo;

Previously, I had the same SQL without the call to the RoundMoney routine
and the query ran fine.

Here is the RoundMoney routine.


Public Function RoundMoney(ByVal curAmount As Currency) As Currency
'***rounds monetary values sent it to 2 decimal places
RoundMoney = Int(curAmount * 100 + 0.5) / 100
End Function

All help you can offer is greatly appreciated.

Thanks,

Seth
 
Try this syntax:

SELECT tblOrder.OrderNo,
sum(IIf(RoundMoney(nz([quantity])*nz([price]))>0,RoundMoney(nz([quantity])*nz([price])),0)) AS Service
FROM tblService RIGHT JOIN tblOrder ON tblService.OrderNo = tblOrder.OrderNo
GROUP BY tblOrder.OrderNo;

the nz validates that you do not have nulls in your fields
 
Back
Top