aka the Pilot Posted March 5, 2006 Posted March 5, 2006 I am very much a novice at this, so bear with me. I am using search and replace in a script to copy a sum (from a number field that is set to round off at the second decimal) in to a text field, replacing a part of the text there. My problem is that when the number ends with a zero (ie 123.40) the zero is missing in the text (ie 123.4). These are prices, so it's confusing for users without the second decimal; an unacceptable situation. I have the feeling there must be a very simple solution for this, but I can't find/think of it. Help!
Reed Posted March 6, 2006 Posted March 6, 2006 I don't know whether this solution is simple, or if it catches all possibilities, but here's a calculation that can convert a number field into a properly formatted text field. (File attached as well...) Case( not Mod(Round(number;2);1);Round(number;2)&".00"; //whole dollars get zeros added Mod(Mod(Round(number;2)*100;100);10);Round(number;2); //numbers not in whole dimes just get rounded Round(number;2)&0 //the rest are numbers in whole dimes, so they get an extra zero ) HTH, Dana roundtext.fp7.zip
comment Posted March 6, 2006 Posted March 6, 2006 You can do this "wholesale", without checking: Int ( Amt ) & "." & Right ( "00" & Amt * 100 ; 2 ) where Amt is non-negative and pre-rounded to 2 decimal places. Neither one will work with copy, BTW. Try using Substitute() instead.
aka the Pilot Posted March 8, 2006 Author Posted March 8, 2006 Thanks ever so much comment, it works like a charm. I even understand what it's doing, though I never would have thought of it on my own. Now I have another problem, which didn't occur (to me) until now becasue I stupidly only tested this with prices of less than 1000. I need the prices with separators, ie 1000 should read 1.000, the separating point is not being transferred from the number field when I substitute. I did find something by searching which looks like it would be in the direction I need, but I am not sure how to apply it in my situation. Let ( [ // USER DEFINED: input = numberfield ; precision = 2 ; currency = "$" ; separator = "," ; decPoint = "." ; // DO NOT MODIFY FROM THIS POINT ON inputRound = Round ( input ; precision ) ; Amt = Abs (inputRound ) ; Dollars = Int ( Amt ) ] ; // PREFIX Case ( inputRound < 0 ; "-" ) & currency & // BILLIONS Choose ( Dollars < 10 ^ 9 ; Right ( Div ( Dollars ; 10 ^ 9 ) ; 3 ) & separator ) & // MILLIONS Choose ( Dollars < 10 ^ 6 ; Right ( Div ( Dollars ; 10 ^ 6 ) ; 3 ) & separator ) & // THOUSANDS Choose ( Dollars < 10 ^ 3 ; Right ( Div ( Dollars ; 10 ^ 3 ) ; 3 ) & separator ) & // UNITS Right ( Dollars ; 3 ) & // FRACTION Choose ( precision < 1 ; decPoint & Right ( 10 ^ precision & Amt * ( 10 ^ precision ) ; precision ) ) ) Please forgive my stupidity and help me once again! :)
comment Posted March 8, 2006 Posted March 8, 2006 It looks somewhat familiar... You would apply this the same way as any other formula - paste it into the calculation window, change "numberfield" to the name of your field, and that's it. If you want, you can modify the other parameters marked as user-defined, e.g. change "$" to "£" or whatever currency you use.
aka the Pilot Posted March 9, 2006 Author Posted March 9, 2006 Thank you, thank you, thank you. I am the guy who has no real idea but a reputation for halfway logical thinking (undeserved, I am sure) at my office, so I got the job of creating our first database. You've helped me get the last problems solved. Thank you.
Recommended Posts
This topic is 6836 days old. Please don't post here. Open a new topic instead.
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now