SQL Update query error Cannot convert char value to money

[Zedster]

I am trying to update a field in one table based on the value of a a field in another table divided by another field in that table.

I am getting the following error:

Msg 235, Level 16, State 0, Line 10
Cannot convert a char value to money. The char value has incorrect syntax.

The error highlights the first line in the code below. Both [Total] fields are money and [Exchange Rate] is real. I am confused.

UPDATE [tblPurchaseOrderCustomFields]
SET [tblPurchaseOrderCustomFields].Total = p.[Total]/p.[Exchange Rate]
FROM [tblPurchaseOrderCustomFields] c
INNER JOIN [tblPurchaseOrdersCurrent] p
ON c.PurchaseOrderRef = p.[PO Number]
WHERE c.Total NOT IN
(
SELECT [PurchaseOrderRef] 
FROM [tblPurchaseOrderCustomFields]
GROUP BY [PurchaseOrderRef]
HAVING COUNT(*) > 1
)

 

[sonic8]

Msg 235, Level 16, State 0, Line 10
Cannot convert a char value to money. The char value has incorrect syntax.

This looks suspicious:

[...]
WHERE c.Total NOT IN (
  SELECT [PurchaseOrderRef] 
[...]

 

[arnelgp]

maybe you can Convert() the other to money.

 

[Zedster]

This looks suspicious:

[...]
WHERE c.Total NOT IN (
  SELECT [PurchaseOrderRef] 
[...]

Of course, stupid me should be WHERE c.PurchaseOrderRef NOT IN.

It would have helped if double clicking the error message would have took me to that line instead of the very first line.