Pad with leading zeros

[Bennie47250]

Using Crystal version 7.0

We have a program that stores everything as text and the ticket numbers are stored like “00123456” or it could be “00000123”. Whenever I create a report that prompts for the ticket number, I have to remind the user to enter the leading zeros. Is there a way to have the number they enter into the parameter have the leading zeros added if they fail to enter them?

Thanks
Bennie

 

[nikhilr]

Hi,
If you have all the ticket number as number only then you can keep your parameter as number and

use it like

{Ticket Number Param} = tonumber(Database Field)

in record selection formula.
Regards
Nikhil

 

[synapsevampire]

The problem with that approach is that the SQL won't get passed to the database, try on of the following:

Change the param to a number to simplify data entry, and convert the number parm to a string in a formula using:

@numberformula
replicatestring("0",8-len(totext({?somenum},0,"&quot))+totext({?somenum},0,"&quot

Then in the record selection use:
{table.field} = @numberformula

OR

Just use this same theory to adjust the length of the string entered in a formula:

@adjustnumber
replicatestring("0",8-len(trim({?somenum})))+trim({?somenum})

Then in the record selection use:
{table.field} = @adjustnumber

If you have the column indexed, this should give the best performance.

OR

Create a SQL Expression and convert the field to a number, in SQL Server you could use:

cast(table."textnum" as numeric)

Then in the record selection use:

{#SQLNumeric} = {?parm}

-k

 

[kenhamady]

Or you could use an Edit mask of:

0000000000

If they don't enter that many digits the parameter will complain. I don't think the parameter can fill them in automatically.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com

 

[Reebo99]

Sorry Synapse, instead of :
@numberformula
replicatestring("0",8-len(totext({?somenum},0,"&quot))+totext({?somenum},0,"&quot

Use :
@numberformula
totext({?somenum},"00000000&quot






Reebo
Scotland (Sunny with a Smile)

 

[Bennie47250]

Thanks everyone for your suggestions however when I attempt to use the formulas, the report runs very slow. I don't think the users would want to sacrifice the running time for the convenience.

Bennie

 

[kenhamady]

The edit mask will not slow the report because it does not use a formula. It just makes sure the user's entry is full length before it will accept the entry.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com

 

[Ngolem]

Ken - the masking in the Parameter does not automatically pad the left with zeros which may annoy the user a bit. It just makes sure that the correct number of digits are entered.

I would use Reebo's solution ..... Have the parameter a numberType and then use

@TicketNumber

totext({?TicketNumber},"00000000"

There should be little if any overhead in this and no annoyance to the user

Jim Broadbent

 

[kenhamady]

The overhead comes in the fact that the formula prevents the criteria from getting to SQL. I am pretty sure that is what is slowing down the report.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com

 

[Ngolem]

true...I didn't think of that...then your solution is the best as far as getting the parameter to the database but I do see a minor annoyance of the user having to determine how many zeros to add before putting in the actual ticket number....

I wonder if it is possible to use an SQL expression and ToChar to accomplish this part of it?

Jim Broadbent

 

[synapsevampire]

Ken: Exactly.

If the data is stored as a string, then you will likely get the best performance by referencing a string in the record selection formula.

I'm not sure whose solution you tried, but mine will pass the SQL, hence optimal performance.

-k