Spacing fields while concatenating in a Cyrstal Formula

I'm using the following formula to concatenate some fields:

DateValue ({totals.applydate}) & " " &
{totals.personnum} & " " &
{totals.accountname} & " " &
{totals.accountdsc} & " " &

The size of the accountname and accountdsc fields vary but I want the fields to line up in the report.

How can I make the accountname and accountdsc fields be always 25 characters, left justify with spaces?

I tried left({total.accountname},25) but it didn't work.

I'm Crystal Version 8.5 SP3

Thanks
kronoscanadaAsked:
Who is Participating?
 
bdreed35Commented:
Are you using a fixed width font like Courier New?  A non fixed width font could keep them from lining up.

Also, use the space function to ensure you have 10 spaces:

DateValue ({totals.applydate}) & " " &
{totals.personnum} & " " &
left({totals.accountname} & space(10),10) &
left({totals.accountdsc} & space(10),10)
0
 
mlmccCommented:
try using where there are 25 spaces ' '

left({total.accountname} & '                               ',25)

mlmcc
0
 
bdreed35Commented:
Is there a reason you are concatenating the fields together?  You can get the fields to line up just by placing each individual field in the details section.

If you need to concatenate them, we can write a formula to accomodate this, just keep in mind that you will need to change the font to a fixed width font like Courier New.

DateValue ({totals.applydate}) & " " &
{totals.personnum} & " " &
{totals.accountname} & " " &
{totals.accountdsc} & ReplicateString (space(1), 25-length({totals.accountname})-length({totals.accountdsc} )) & " " &

I left the last "&" because I assumed there was more to the formula based upon what you said in your post.
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
bdreed35Commented:
mlmcc's method will work good too, in fact I think I like it better than the replicate string function that I used.  
The only modification I would make is to use the space function instead of trying to hit the spacebar 25 times:

left({total.accountname} & space(25),25)
0
 
kronoscanadaAuthor Commented:
Almost there, I tried:

DateValue ({totals.applydate}) & " " &
{totals.personnum} & " " &
left({totals.accountname} & '          ',10) &
left({totals.accountdsc} & '          ',10)

I'm using 10 instead of 25 just for testing and for this question.

The output looks like this:

10/11/2004 10001 12345   Toys
10/13/2004 10001 123456789 Kitchen

Line one does not have 5 spaces after the accoutname (12345). Line two looks good.
0
 
kronoscanadaAuthor Commented:
Wow, you guys are fast! and good!

I'm using:

DateValue ({totals.applydate}) & " " &
{totals.personnum} & " " &
left({totals.accountname} & space(10),10) &
left({totals.accountdsc} & space(10),10)

With Courier New (Western) and it works!

I'm new to this site but it looks like mlmmc and bdreed35 should split the points, 75 each. I need to figure out how to do that.

I thought someone would ask why I'm concatenating in the first place.  I put these fields in a Cross Tab and the row labels would not repeat if there where the same, so I concatenated the fields and now every row is unique.



0
 
mlmccCommented:
Glad i could help

mlmcc
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.