SQL Update query error Cannot convert char value to money (1 Viewer)

Zedster

Registered User.
Local time
Today, 11:26
Joined
Jul 2, 2019
Messages
168
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.

Code:
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

AWF VIP
Local time
Today, 12:26
Joined
Oct 27, 2015
Messages
998
Msg 235, Level 16, State 0, Line 10
Cannot convert a char value to money. The char value has incorrect syntax.

This looks suspicious:
Code:
[...]
WHERE c.Total NOT IN (
  SELECT [PurchaseOrderRef] 
[...]
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 19:26
Joined
May 7, 2009
Messages
19,169
maybe you can Convert() the other to money.
 

Zedster

Registered User.
Local time
Today, 11:26
Joined
Jul 2, 2019
Messages
168
This looks suspicious:
Code:
[...]
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.
 

Users who are viewing this thread

Top Bottom