Trimming and Concatenation

[arodri]

Hello!

I have a "Location" field coming into my report from our sql database. It is coming in as a number, so I think for trimming and concatenation I might have to either convert it ToText or Cstr..?
This field ranges from 1 to 3 integers (ex. 7,10,601, etc) but I need it to be a fixed length of 4, with zeros being added to the left. For example, using my example above, this is what my new field would look like:
0007
0010
0601
So the number of zeros being added to its left would depend on what the Location is. If the location is just 7, then I would need to add 3 zeros to the left, if it's 601 then I would only need to add one.

I thought I would have to add a set number of zeros to the left first, and then trim the field so that the length is only four, but my formulas aren't coming out right.

Any ideas??

Thanks!

 

[MCuthill]

arodi,

The following should provide what you are seeking.

IF {Table.YourField}<10 THEN "000" & {Table.YourField} ELSE
IF {Table.YourField}<100 THEN "00" & {Table.YourField} ELSE
IF {Table.YourField}<1000 THEN "0" & {Table.YourField} ELSE 
ToText({Table.YourField})

I didn't have a chance to run this through Crystal, so it may need some slight adjustments.

Hope this helps!


Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[lbass]

You can use:

totext({table.number},"0000")

-LB

 

[arodri]

MCuthill,

Thank you so much! That worked perfectly. I had to create another formula to pick up only the first 4 integers since conveting it to text adds ".00" but it's exactly what I was looking for.

Thanks again!