Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 09-19-2019, 11:37 PM   #1
Zedster
Newly Registered User
 
Join Date: Jul 2019
Location: UK Warwickshire
Posts: 47
Thanks: 22
Thanked 0 Times in 0 Posts
Zedster is on a distinguished road
SQL Update query error Cannot convert char value to money

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
)

Zedster is offline   Reply With Quote
Old 09-19-2019, 11:50 PM   #2
sonic8
AWF VIP
 
Join Date: Oct 2015
Posts: 269
Thanks: 41
Thanked 76 Times in 73 Posts
sonic8 is on a distinguished road
Re: SQL Update query error Cannot convert char value to money

Quote:
Originally Posted by Zedster View Post
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] 
[...]
__________________
New article:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
sonic8 is offline   Reply With Quote
The Following User Says Thank You to sonic8 For This Useful Post:
Zedster (09-20-2019)
Old 09-19-2019, 11:55 PM   #3
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,568
Thanks: 68
Thanked 2,744 Times in 2,629 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: SQL Update query error Cannot convert char value to money

maybe you can Convert() the other to money.

__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 09-20-2019, 12:13 AM   #4
Zedster
Newly Registered User
 
Join Date: Jul 2019
Location: UK Warwickshire
Posts: 47
Thanks: 22
Thanked 0 Times in 0 Posts
Zedster is on a distinguished road
Re: SQL Update query error Cannot convert char value to money

Quote:
Originally Posted by sonic8 View Post
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.

Zedster is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
[SOLVED] Convert 3-char month & year to Date ALewis06 Queries 3 08-17-2015 10:47 AM
Convert numeric to money Joe8915 SQL Server 4 02-03-2010 09:12 AM
update query to convert time and minutes trythis Queries 1 07-27-2009 12:43 PM
Help Designing an Update Query to Convert Currencies Please M_S_Jones Queries 8 09-17-2008 12:48 AM
Char by char field controle ? jvincent General 4 12-07-2005 12:21 AM




All times are GMT -8. The time now is 03:50 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World