(Padding) Displaying a number with a fixed number of digits 1

[scottlacourse]

I am creating a crystal report for export to an ascii file and need to display the record count field as a fixed 5 digits including the leading zeros - ie record one is 00001 and record 98 is 00098, etc. Can anyone help?

 

[DallasAggie]

Scott,

I just tried this out...I'm not sure if there is a shorter way to do this, but I think it'll give you an immediate solution:

If the field you want to have with leading zeros will always be the same length, then try this:

1. Create a Formula: Name whater you like
2. Do a nested If-Then statement with the your field...
example:
if Length ({Boeing_SSA.SerialNumber}) = 5 then {Boeing_SSA.SerialNumber} //this is max length of my field
else if Length ({Boeing_SSA.SerialNumber}) = 4 then '0' + {Boeing_SSA.SerialNumber}
else if Length ({Boeing_SSA.SerialNumber}) = 3 then '00' + {Boeing_SSA.SerialNumber}

..etc
...etc
....etc.
3. Drop the formula field in instead of field you are using now.


I just did this on one of my reports and it seemed to work fine...hope this helps.

Robert Andrade
The Boeing Company

 

[scottlacourse]

Ahhh.....genius. I've been trying similar work arounds, I'll try that one now.....

Thank you!!!

 

[MVisconte]

How 'bout:

right("0000" & {Boeing_SSA.SerialNumber}, 5)

? ;-)

 

[scottlacourse]

Actually ran into a snafu with the length formula - it requires a string. The result of the running count formula is a number - I am playing with conversion now, but this gets pretty laborious......

 

[pelajhia]

Sounds like you are in good shape, but here is a slightly different approach, just in case:
Break down this big formula into 5 formulas (digit1,digit2,digit3,etc.), each stripping out one of the 5 digits for comparison.
eg: iif(mid(totext(MyNumber),1,1)="","0",mid(totext(MyNumber),1,1))
That is, if the first digit is blank, return "0",
otherwise just return the Digit, in text format


Once you get all your formulas working, you can concatenate
them in one so that your output to ascii is nice and clean.
(eg: digit1+digit2+digit3...)

Good luck!

 

[JoeCrystal]

If your record number field is numeric:

ToText({RecordNumberField},"00000&quot

If your record number field is a string:

ToText(ToNumber({RecordNumberField}),"00000&quot

 

[scottlacourse]

Thanks a bunch all - both the right and totext functions above do exactly what I am looking for.

 

[mbarron]

One more for you:

replicatestring("0",5-length({your.field}))&{yourfield}


MVisconte,
Very nice! Simple - yet elegant -I would have never thought of that.

Joe,
I keep forgetting about that way. Thanks for the reminder.
Mike
If you're not part of the solution, you're part of the precipitate.