Active TopicsMemberlistSearchHelpRegisterLogin

Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions
Message Icon Topic: NumericText function returning letters when looped Post Reply Post New Topic
Author Message
CrystalGoon
Newbie
Newbie
Avatar

Joined: 24 Jan 2013
Online Status: Offline
Posts: 5
Quote CrystalGoon Replybullet Topic: NumericText function returning letters when looped
     Posted: 24 Jan 2013 at 5:07am
I have a dataset that includes a free text column.  Often, people put a 5 digit product number into the free text.  I want to grab just the 5 digit product number from the text field.  I am using CR 2008
 
I have the logic below, but it is returning values that are not numbers.
 
e.g.
  • if 12345 is in the middle or at the end of the string, the formula returns " 1234" and mistakenly includes the <space>.
  • If there are no 5 digit codes in the string, the formula returns a period "." or the last letter in the string.
Interestingly, if 12345 is at the beginning of the string, the formula correctly returns 12345.

Formula:
Local StringVar inString := {db.text};
Local NumberVar strLen := Length (inString);
Local NumberVar i := 1;
While i <= strLen  Do
(
   Local StringVar outString:= mid({db.text},i,5);
   If NumericText (outString) then exit while;
   i := i + 1;
);
outString;
 
Data:
My test data set is a .xls with the following data points:
 
Test text with numbers at the end 12345
12345 test text with numbers at the beginning
Text with five numbers 12345 in the middle.
12345
Text only - but with other ch^racter$!
 
Pathetic cry for help:
This is my first post to this forum.  Anyone have any suggestions?


Edited by CrystalGoon - 28 Jan 2013 at 10:28am
IP IP Logged
Sastry
Moderator
Moderator
Avatar

Joined: 16 Jul 2012
Online Status: Offline
Posts: 537
Quote Sastry Replybullet Posted: 28 Jan 2013 at 8:51pm
Hi

Try this :

Local StringVar inString := replace({db.text}," ","");
Local NumberVar strLen := Length (inString);
Local NumberVar i := 1;
While i <= strLen  Do
(
   
    Local StringVar outString:= mid(instring,i,5);
    If  NumericText (outString) then exit while;
    i := i + 1;
);
outString;
Thanks,
Sastry
IP IP Logged
CrystalGoon
Newbie
Newbie
Avatar

Joined: 24 Jan 2013
Online Status: Offline
Posts: 5
Quote CrystalGoon Replybullet Posted: 30 Jan 2013 at 2:39pm
Thank you for getting back to me.  I tried what you wrote.  I was able to get the numbers at the beginning and the end of the string to work, but not in the middle.  Also, strings without numbers are still returning the last letter only.  Examples:
 
This text:
maybe at the end
Returns:
d
 
This text:
Sometimes, 12345 in the middle
Returns:
,1234
 
BUT
This text:
This is a test of 12345
Returns this:
12345
 
- and -
This text:
12345 is fun
Returns this:
12345
 
which is correct!
------------------------
 
I don't understand how a single letter would pass the NumericText() function.
 
I also don't understand why the number in the middle of the string would include the character before the numbers.
 
Please help!
IP IP Logged
Sastry
Moderator
Moderator
Avatar

Joined: 16 Jul 2012
Online Status: Offline
Posts: 537
Quote Sastry Replybullet Posted: 31 Jan 2013 at 6:07pm
Hi

Try this :

Local StringVar inString := replace("This is a, 12.34,5 string text"," ","");
Local Stringvar instring1:=replace(replace(instring,".",""),",","");
Local NumberVar strLen := Length (inString1);
Local NumberVar i := 1;
While i <= strLen  Do
(
  
    Local StringVar outString:= mid(instring1,i,5);
    If  isnumeric(outString) then exit while;
    i := i + 1;
);
if isnumeric(outString) then
outstring else " ";


1. It is printing single character because your while loop is checking for length of the string, once it reaches the length then it is printing whatever it is holding.

2. It is treating ',' or '.' are numeric values because it uses ',' (coma) for thousand separator and '.' to display decimal places.

Now the above formula will take care of all the above and give you only numeric values.

Thanks,
Sastry
IP IP Logged
CrystalGoon
Newbie
Newbie
Avatar

Joined: 24 Jan 2013
Online Status: Offline
Posts: 5
Quote CrystalGoon Replybullet Posted: 01 Feb 2013 at 4:49am
That's great!  That took care of the strings that actually contained the numbers, but I was still getting output that was a single character.
 
To fix that, I added a length check after the loop finished before printing the variable.
 
this is what finally worked:
 
Local StringVar inString := replace(replace(replace({Sheet1_.text},".",""),",","")," ","");
Local NumberVar strLen := Length (inString);
Local NumberVar i := 1;
While i <= strLen  Do
(
   Local StringVar outString:= mid(inString,i,5);
   If NumericText (outString) then exit while;
   i := i + 1;
);
if length(outString) = 5 then outString else "";
 
 
Thank you for your help!  The key learning for me was that Crystal considers the "," and the "." to be numbers.
 
The trick of getting rid of the spaces in a string was a good tip too.
 
Thanks!
Ian
 
IP IP Logged
CrystalGoon
Newbie
Newbie
Avatar

Joined: 24 Jan 2013
Online Status: Offline
Posts: 5
Quote CrystalGoon Replybullet Posted: 01 Feb 2013 at 5:46am
Even Better -
 
if you adjust the i variable, you don't need the correction at the end.  I'm using i-1 <= strLen:
 
Local StringVar inString := replace(replace(replace({Sheet1_.text},".",""),",","")," ","");
Local NumberVar strLen := Length (inString);
Local NumberVar i := 1;
While i-1 <= strLen  Do
(
   Local StringVar outString:= mid(inString,i,5);
   If NumericText (outString) then exit while;
   i := i + 1;
);
outString;
 
 
IP IP Logged
Printable version Printable version

Forum Jump 		You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum

HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | PRIVACY | CLICK PLAYS | KONACAD

© 2004-2026 Brian Bischof. All Rights Reserved.