Solved

Counting then adding 2 fields

Posted on 2014-12-29
6
88 Views
Last Modified: 2014-12-30
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
Comment
Question by:John-S Pretorius
  • 3
  • 2
6 Comments
 

Author Comment

by:John-S Pretorius
ID: 40522628
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
 
LVL 100

Accepted Solution

by:
mlmcc earned 500 total points
ID: 40523080
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
 
LVL 34

Expert Comment

by:James0628
ID: 40523403
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:John-S Pretorius
ID: 40523447
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)

Excel format
0
 

Author Closing Comment

by:John-S Pretorius
ID: 40523449
Thank you.
0
 
LVL 34

Expert Comment

by:James0628
ID: 40523544
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

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now