Don't know for sure, but could it be that the fields where you're adding 27.5 are set up as integer fields? This might not have been a problem in Access where there's just a 'number' datatype, but could make a difference in sql with 'int', 'float', 'decimal' etc
If you've auto-upsized your access db to SQL and all the values in an access 'number' field were integers, the upsizer would probably create a SQL field with type 'int'. HTH Alex > -----Original Message----- > From: Ed Gordon [mailto:[EMAIL PROTECTED]] > Sent: 03 October 2002 03:30 > To: CF-Talk > Subject: SQL error - Arithmetic overflow...? > > > This ran just fine using Microsoft Access. Now I get an overflow? > > Can you spot what's wrong? The values in the fields aren't > very big - the > largest is like 1952 to which I'm adding 20 or 30 or so... > > ============= > ODBC Error Code = 22003 (Numeric value out of range) > > > [Microsoft][ODBC SQL Server Driver][SQL Server]Arithmetic > overflow error > converting numeric to data type numeric. > > > SQL = "Update MEMBERS Set status='Gold', current_point_ratio=1.25, > total_purchases=total_purchases+22, > purchases_this_year=purchases_this_year+22, > purchases_quarter_1=purchases_quarter_1+22, > total_points_earned=total_points_earned+27.5, > points_earned_this_year=points_earned_this_year+27.5, > points_earned_quarter_1=points_earned_quarter_1+27.5, > point_balance=point_balance+27.5, last_tran_posted_on={d > '2002-10-02'}, > last_tran_posted_at={t '21:16:33'} Where > uuid='23A99597-9F04-11D6-AEAA00A0CC617FCD'" > ============ > > I guess it's too much to ask that they tell me WHAT value is > out of range - > or, since I am now using a "real" SQL database instead of MS > Access, do I > have to do all the calculations outside of the Update? > > Thanks in Advance, > > Ed Gordon > > > > ============= > HERE'S THE CF CODE > ============= > <CFQUERY datasource="#application.ds#"> > Update MEMBERS > Set status='#STATUSCHANGE#', > current_point_ratio=#RATIOCHANGE#, > total_purchases=total_purchases+#numAmount#, > purchases_this_year=purchases_this_year+#numAmount#, > purchases_quarter_1=purchases_quarter_1+#numAmount#, > total_points_earned=total_points_earned+#numPoints#, > points_earned_this_year=points_earned_this_year+#numPoints#, > points_earned_quarter_1=points_earned_quarter_1+#numPoints#, > point_balance=point_balance+#numPoints#, > last_tran_posted_on=#CREATEODBCDATE(tdazedate)#, > last_tran_posted_at=#CREATEODBCTIME(theodbctime)# > > Where uuid='#getmember.uuid#' > > </CFQUERY> > ============= > HELP? > > TIA > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm
