Rick, You'll find commas can give trouble as well. You want 25000 and the user types in $25,000.50.
I use cfinput and validate to 'float'. I also do this to the same value on the form processing page: <CFSET form.Value = REPLACE(form.Value,",","","ALL")> <CFSET form.Value = REPLACE(form.Value,"$","","ALL")> Then before you store the value, do this: <cfset form.value=form.value*100> When you display the value again, convert it back to human-readable format When you calc with it, bear in mind that the value is in cents (or pence etc.). --Matt Robertson-- MSB Designs, Inc. http://mysecretbase.com -----Original Message----- From: Rick Faircloth [mailto:[EMAIL PROTECTED]] Sent: Tuesday, December 17, 2002 10:10 AM To: CF-Talk Subject: RE: mySQL equivalent of MS Access "Money" fieldtype? I'm running into some of the very problems you mention. I tried "Decimal" as the field type, but 100.5 gets rounded up to 101.00...not good. I tried Integer, but when, for example, 100.50 gets entered into a formfield, then inserted into the db, it gets truncated to 100. How do I set up Integer to retain 2 decimal places? Also, I'm trying to setup the mySQL db fieldtype and CF processing such that if a user inputs $100.50, which is not a number, mySQL won't reject it. I tried setting up the CF input line with various combinations of functions, such as: <cfqueryparam cfsqltype="CF_SQL_INTEGER" Value="#LSCurrentFormat(LSParseNumber(Form.UCPaymentAmount))#"> but I haven't been able to find the right combo to allow users to input either 100.50, 100.5, or $100.50 and still get the right number into the db without rounding or truncating and back out for display on the CF page. Clues? Rick -----Original Message----- From: Sean A Corfield [mailto:[EMAIL PROTECTED]] Sent: Tuesday, December 17, 2002 12:26 PM To: CF-Talk Subject: Re: mySQL equivalent of MS Access "Money" fieldtype? On Tuesday, Dec 17, 2002, at 07:41 US/Pacific, Stephen Moretti wrote: > INT is INTEGER which means that there's no decimal places.... Not > much good > for real money that I'm afraid. Integer is the safer way to represent money - as pennies - because that way you avoid rounding errors. Financial applications should never use floating point to represent dollars (or whatever). If you take 0.00 and add 0.01 a hundred times, you're quite likely to get something which does not equal 1.00 because of inherent inaccuracies in floating point representation. Sean A Corfield -- Director, Architecture Web Technology Group -- Macromedia, Inc. tel: (415) 252-2287 -- cell: (415) 717-8473 aim: seancorfield -- http://www.macromedia.com An Architect's View -- http://www.corfield.org/blog/ Introducing Macromedia Contribute. Web publishing for everyone. Learn more at http://www.macromedia.com/contribute ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Get the mailserver that powers this list at http://www.coolfusion.com
