Fortunately, this is an internal business app and will only deal with US currency. Perhaps, one day I'll be "in the big leagues" and need to worry about international currencies... :o)
Thanks for everyone's help! Rick -----Original Message----- From: Costas Piliotis [mailto:[EMAIL PROTECTED]] Sent: Tuesday, December 17, 2002 3:19 PM To: CF-Talk Subject: RE: mySQL equivalent of MS Access "Money" fieldtype? I'd also test for european number formats. Remember that $1.00 in US is $1,00 in Europe... That's really where the LSParseCurrency function shines (I think)... -----Original Message----- From: Rick Faircloth [mailto:[EMAIL PROTECTED]] Sent: Tuesday, December 17, 2002 11:56 AM To: CF-Talk Subject: RE: mySQL equivalent of MS Access "Money" fieldtype? Thanks for the tip, Costas and everyone. Here's what appears to be finally working: mySQL fieldtype set to: Decimal (2 decimals) For outputting to screen: <CFOUTPUT>#DollarFormat(Val(LSParseCurrency(Form.UCPaymentAmount)))#</CFOUTP UT> For submitting to database: <cfqueryparam cfsqltype="CF_SQL_DECIMAL" value="#Val(LSParseCurrency(Form.UCPaymentAmount))#"> My tests have shown this will output to the screen the proper amount with dollar formatting and will input into the database a valid number with up to 2 decimal places whether the user puts 100.55 or $100.55 into the formfield. It's looking good so far. Are there any pitfalls that I'm overlooking? Rick -----Original Message----- From: Costas Piliotis [mailto:[EMAIL PROTECTED]] Sent: Tuesday, December 17, 2002 1:47 PM To: CF-Talk Subject: RE: mySQL equivalent of MS Access "Money" fieldtype? If I'm not mistaken, there's a float or decimal data type in mysql... Trying to remember off the top of my head. Use that data type. Also, try this: #val(Form.UCPaymentAmount)# It will remove all unnecessary formatting and convert the form value to a number each time. Should work fine for what you need. Just make sure you round your numbers off consistantly all the time... That's the real place you need to be careful at. -----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 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
