Converting a text field to a number

Date: Archived
Product/Release: Crystal Reports for LANSA
Abstract: The ToNumber string function in Crystal Reports provides an easy way to convert numbers in a text field to a number field
Submitted By: LANSA Technical Support

Numbers with which calculations are performed are typically stored in numeric fields. However, sometimes it may happen that it is necessary to perform calculations on a field, or part of a field, that is stored in a text (alphanumeric) field. LANSA's string operations and virtual fields may be of help in this situation, but when certain calculations are needed for just one or two specific reports, it may be easier to use Crystal Report's ToText string function.

The ToNumber function has the following syntax: "ToNumber (x)", where x is a text string that holds the numeric text.

To be sure the text contains only numbers, test on numeric first, before doing any calculations (non numeric data may cause your report to end in an error). The syntax for testing on numeric is: "If NumericText (x)", which returns a true or false.

Further indicate the specific positions of the field if there is a need to convert or test only part of the string.

Example 1:

A retailer has a customer list that includes both customers living inside the state and outside. The Postal Codes beginning with two letters, followed by a 5 digit number. In other words, postal codes look like "XX99999". The two letters do not indicate the area, but the numbers behind it do. The company has a sales tax structure based on area.

If NumericText ({customer.POSTAL CODE}[3 to 7]) Then
If ToNumber({customer.POSTAL CODE}[3 to 7]) in 92200 to 92399
Then "7.5"
Else "6.5"
Else "0"

Example 2:

The year a customer joined isn't kept anywhere else then in the 4th and 5th digit of the customer number. Now you want to use that year in a personalized letter. Assume the current year is 98.

If {file.CUSTOMER NUMBER}[4 to 5] < "98" Then
"You have been a valued customer for " + ToText(98 - ToNumber({file.CUSTOMER NUMBER}[4 to 5]))
+ " years."
"You are one of our newer customers, and we want you to know how valuable you are to us."

Example 3:

Date related examples using the ToNumber and ToText functions can be found in a tip called Converting and Formatting a date field in the LANSA Client tips section.