| Author |
Message |
thummel1
Senior Member
Joined: 27 Apr 2012
Location: United States
Online Status: Offline
Posts: 140
|
   Topic: adding leading zeroes to data field
Posted: 13 Aug 2012 at 6:50am |
I am in Crystal Reports 2008. Do any of you know how I could apply the Excel formula below, but in Crystal?
=text(A1,”0000000000”)?
I need to populate a data field with 10 digits. If there are less than 10 digits in the data field, it must contain leading zeroes, for a total of 10 digits.
Any auggestions?
Edited by thummel1 - 13 Aug 2012 at 7:53am
|
|
"Press any key to continue. Where's the 'Any' Key?" ~Homer Simpson
|
 IP Logged |
|
|
SonoItaliano
Newbie
Joined: 02 Aug 2012
Location: United States
Online Status: Offline
Posts: 9
|
Posted: 13 Aug 2012 at 8:41am |
|
Create a formula field:
ToText ({YOUR_NUMERIC_FIELD},"0000000000" )
Then display the formula field in your report in place of the original numeric field.
|
|
IP Logged |
|
thummel1
Senior Member
Joined: 27 Apr 2012
Location: United States
Online Status: Offline
Posts: 140
|
Posted: 13 Aug 2012 at 9:12am |
I tried this, sp my formula looks like this:
Totext({PRGARNHIST.VENDOR},"0000000000")
and I get the error message "Too many arguments have been given to this function".
|
|
"Press any key to continue. Where's the 'Any' Key?" ~Homer Simpson
|
|
IP Logged |
|
SonoItaliano
Newbie
Joined: 02 Aug 2012
Location: United States
Online Status: Offline
Posts: 9
|
Posted: 13 Aug 2012 at 10:31am |
|
I have no explanation; it worked for me exactly as shown (I copied/pasted directly from the formula editor and then dummied up the field name). Here it is again, exactly as in the formula editor:
ToText ({PART.ANNUAL_USAGE_QTY}, "0000000000" )
I am using Crystal Reports XI. You?
You are putting this in the correct place, right? In "Formula Fields" - not SQL Expression Fields, Running Total Fields, etc.
|
|
IP Logged |
|
kevlray
Admin Group
Joined: 29 Oct 2009
Online Status: Offline
Posts: 1587
|
Posted: 13 Aug 2012 at 10:37am |
|
Is {PRGARNHIST.VENDOR} a numeric field?
|
|
IP Logged |
|
SonoItaliano
Newbie
Joined: 02 Aug 2012
Location: United States
Online Status: Offline
Posts: 9
|
Posted: 13 Aug 2012 at 10:39am |
|
Ah, I see. The problem is that your Vendor "number" is not a number at all; it is already a text field.
Try something like this:
If Length({PRGARNHIST.VENDOR}) < 10 then
ReplicateString ("0",10 - Length({PRGARNHIST.VENDOR}) ) & {PRGARNHIST.VENDOR}
else {PRGARNHIST.VENDOR}
|
|
IP Logged |
|
thummel1
Senior Member
Joined: 27 Apr 2012
Location: United States
Online Status: Offline
Posts: 140
|
Posted: 13 Aug 2012 at 3:14pm |
|
I am using Crystal Reports 12.
|
|
"Press any key to continue. Where's the 'Any' Key?" ~Homer Simpson
|
|
IP Logged |
|
thummel1
Senior Member
Joined: 27 Apr 2012
Location: United States
Online Status: Offline
Posts: 140
|
Posted: 13 Aug 2012 at 3:14pm |
No, it's a text field. I will give this formula a try and see how it works...will keep you posted.
|
|
"Press any key to continue. Where's the 'Any' Key?" ~Homer Simpson
|
|
IP Logged |
|
SonoItaliano
Newbie
Joined: 02 Aug 2012
Location: United States
Online Status: Offline
Posts: 9
|
Posted: 14 Aug 2012 at 2:28am |
|
You could probably do it like this as well:
ToText (ToNumber({YOUR_NUMERIC_FIELD}),"0000000000" )
I don't know which would be more cpu efficient...
|
|
IP Logged |
|
thummel1
Senior Member
Joined: 27 Apr 2012
Location: United States
Online Status: Offline
Posts: 140
|
Posted: 14 Aug 2012 at 4:55am |
|
I'll start with the easiest and move my way up. Thanks!
|
|
"Press any key to continue. Where's the 'Any' Key?" ~Homer Simpson
|
|
IP Logged |
|
|
|
