• Status: Solved
• Priority: Medium
• Security: Public
• Views: 102

# Counting then adding 2 fields

I have 2 Fields, {CardNumber} and {FacilityCode}. The {CardNumber} field range from 0 - 9999 (4 digits) and I first need a formula to check the count of digits for the {CardNumber} field and buffer an additional 0(s) in front so there is always 5 digits and combine it as {FacilityCode} + {CardNumber} (See examples below) :

{CardNumber}   'BufferedValue'  {FacilityCode}   CombinedResult
29                               00029                         200                     20000029
229                             00229                         201                     20100229
2229                           02229                         200                     20002229
0
John-S Pretorius
• 3
• 2
1 Solution

Technical Service Manager, Mid-AtlanticAuthor Commented:
The solution is actually embarrassingly easy to mention but I would still like to see what a Crystal reports solution would come to. By adding 2000000 to the {CardNumber} in excel I was able to create a whole column automatically :

2000000 + 2229 = 2002229
2010000 +   229 = 2010229
0

Commented:
You could do the same thing in Crystal

10000 * {FacilityField} + {CardNumberField}

Another way would be

CStr({FacilityField},0,'') + Right("00000"& CStr({CardNumberField},0,''),5)

mlmcc
0

Commented:
FWIW, I don't see how "adding 2000000 to the {CardNumber}" would produce the results you described.

mlmcc's suggestion to multiply FacilityCode and then add them together might be the best solution, but, FWIW, another approach would be:

CStr ({FacilityCode}, "#") + CStr ({CardNumber}, "00000")

James
0

Technical Service Manager, Mid-AtlanticAuthor Commented:
Thank you guys, these solutions is what I was hoping to see. James, FWIW - the solution as described truly works as simple as it sounds, it's more work though as I had to sort all Facility Codes (200,201 and 203)

0

Technical Service Manager, Mid-AtlanticAuthor Commented:
Thank you.
0

Commented:
The thing is, you said that you added 2000000, but then one of your examples is 2010000 + 229.  Adding 2,000,000 (or, technically, 20,000,000) only works when the Facility_Code is 200 (which it happens to be in every line in your screenshot, so, yes, it works in those particular lines).

James
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.