Print Page | Close Window

Zeros to fill

Printed From: Crystal Reports Book
Category: Crystal Reports for Visual Studio 2005 and Newer
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=13619
Printed Date: 02 May 2024 at 10:45pm


Topic: Zeros to fill
Posted By: jbrandes99
Subject: Zeros to fill
Date Posted: 28 Jun 2011 at 9:52am
Me again...in need of assistance please.  I need to figure out a way to create a formula that displays {account_no}+{invoice_total}. The problem I am having is that the results need to be 19 characters, with zeros filling in any characters in the middle. For example:
 
1234567000000234567
 
As always, thanks for your help!


Replies:
Posted By: lockwelle
Date Posted: 30 Jun 2011 at 3:45am
someone else just had this type of issue
toText({table.account_no},0,"") + toText({table.invoice_total},ReplicateString("0",15))
 
or if that doesn't work
toText({table.account_no},0,"") + toText({table.invoice_total},"000000000000000")
 
this was assuming that account # was 4 long and part of the 19 in length.
you can adjust as needed
 
hopefully 1 of them will work

Posted By: jbrandes99
Date Posted: 30 Jun 2011 at 5:59am
When I try either of these formulas I get "too many arguments have been given to this function"

Posted By: lockwelle
Date Posted: 01 Jul 2011 at 3:07am
well, try them one at a time to see which one is bad.  I would suspect the 2nd half, but according to CR's help that should be a valid format string.

Posted By: jbrandes99
Date Posted: 05 Jul 2011 at 6:31am
I trie breaking it down and still get the error when I place
 
toText({table.account_no},0,"")
 
into a new formula.

Posted By: hilfy
Date Posted: 06 Jul 2011 at 12:24pm

I assume you're using the correct table and field name from your database.  I suspect that account_no is not a numeric field - that it's a varchar of some sort.  If that's the case, you don't need "toText".

-Dell


-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics

Posted By: jbrandes99
Date Posted: 11 Jul 2011 at 9:51am
Genius..that was it..thanks so much for your help!

Posted By: jbrandes99
Date Posted: 20 Jul 2011 at 8:22am
I just noticed in testing that using these formulas only shows the whole number, i.e., $1.05 displays as $001.
 
Need a little more help. I need to display the amount as 105 (no currency symbol or decimal just numbers).

Posted By: hilfy
Date Posted: 20 Jul 2011 at 9:52am
Change the '0' in the ToText call to however many numbers you want to display after the decimal point.
 
-Dell


-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics

Posted By: jbrandes99
Date Posted: 20 Jul 2011 at 10:40am
That got the values to display, but I still need to figure out how to get rid of the $ and decimal point from the value. Any takers?

Posted By: hilfy
Date Posted: 20 Jul 2011 at 11:16am
To get 105 instead of 1.05, multiply by 100.  For the $, if this is a currency field, right-click on it and go to "Format...".  On the Number tab, format so that there is no currency symbol.
 
-Dell


-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics


Print Page | Close Window