Are you simply converting the number to a character form or do you need to do this as part of an export? (Say to pass data to another system?). If it's for export there are some facilities provided if you are exporting to a text file. (See "Export and print options" for details. There's a useful section in the Help as well).
If that fails to help, Nick's formula looks fine but I guess you could also get rid of the decimal by multiplying by 100 and taking the integer. Note that the leading zero fill Nick has used is a feature of Version 7. If using an earlier version an alternative approach is required to get you leading zero.
So, if you don't have V7 but are always including a single leading zero the concatenation (Character field required) is simply to + the "0" with the new field. Something like
"0"+str(NUMBER_field*100) ought to be close.
On the other hand if there are a different numbers of leading zeros required depending upon the value in "Number_field" a different approach is required. IIRC I have seen a very elegant solution somewhere but can't quite remember where or how to find it at the moment.
One way is to take the de-decimalized integer number, add it to 1,000,000 and convert the result to a character basis using STR. The use the RIGHT function for 6 chars to get rid of the "1" at the beginning! Something like:
The other way it to check the length of the de-decimalized string and work out how many zeros need to be added at the beginning rather than assuming it will always be one.
V7 or the Export Options solution are certainly the easiest solutions to think through.
I hope this helps.
Thanks, Grant. I can always count on you for a more detailed explanation.
You're right, I forgot that the Fill option is a v7 feature, so yes, a more elaborate approach is necessary.
Also, I believe the trick for adding leading zeroes was in one of the Monarch Newsletters. Maybe one of the Datawatchers could post the formula here?
[size="1"][ December 03, 2003, 09:35 PM: Message edited by: Nick Osdale-Popa ][/size]
The formula for adding the correct number of "0"s to pad a character field is
The conversion to strip decimals from a numeric field and pad with "0"s would be
That's the one!
Thanks Steve. I knew I would forget it. It's quite similar to the 'add to a million' (or suitably much larger number) method and somehow I always fixate on the numerical route. (Especially when posting at some ridiculous time of night. )
I will have to practice more ...
[size="1"][ December 04, 2003, 04:20 AM: Message edited by: Grant Perkins ][/size]