Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2016 : Report Design  
Message Icon Topic: Need both Alpha and Numeric info from a field Post Reply Post New Topic
Author Message
cheryla
Groupie
Groupie


Joined: 23 Oct 2013
Online Status: Offline
Posts: 48
Quote cheryla Replybullet Topic: Need both Alpha and Numeric info from a field
    Posted: 30 Aug 2017 at 8:21am
I have a string field that has both alpha and numeric information in it. I need a formula that will give me the alpha info and another to give me the numeric info. This field is a user defined field so 2 types of info are entered. Below is a sampling of the data.

S99
015
D15
SR21
O-R86
O1
4
D-R366
DR486

AS you can see the info varies. For us the letters mean one thing and the numbers mean something else. I need to make 2 different fields with this data (for use in other formulas).

Also, the numeric data must be able to be summarized somehow. I've tried several different things but nothing has worked so far. This forum has always helped me in the past.I'm hoping it can help again.

Thanks in advance! Let me know if you need more info. I need to get this resolved within the next day or so as it is a report to be used at month's end.
IP IP Logged
kevlray
Moderator
Moderator
Avatar

Joined: 29 Oct 2009
Online Status: Offline
Posts: 1331
Quote kevlray Replybullet Posted: 30 Aug 2017 at 12:46pm
You will need two formulas.  The first gets the numeric value, the second gets the characters.  Replace "test" with your field.

dim  i as number
for i =1 to len(test)
      if isnumeric(mid(test,i,1)) then
          exit for
    end if
next i

formula = cdbl(mid(test,i))


for i =1 to len(test)
      if isnumeric(mid(test,i,1)) then
          exit for
    end if
next i

formula = left(test,i-1)
IP IP Logged
cheryla
Groupie
Groupie


Joined: 23 Oct 2013
Online Status: Offline
Posts: 48
Quote cheryla Replybullet Posted: 31 Aug 2017 at 2:51am
I created 2 formulas based on what you gave me. The numeric formula is telling me that "The remaining text does not appear to be part of the formula" and it highlights the entire formula. Also, the alpha formula tells me that in the first line "for i =1... that "a variable name is expected here" for the i. Any ideas on how to resolve these 2 issues?

thanks again.
IP IP Logged
kevlray
Moderator
Moderator
Avatar

Joined: 29 Oct 2009
Online Status: Offline
Posts: 1331
Quote kevlray Replybullet Posted: 31 Aug 2017 at 6:08am
First did you use Basic syntax (I lost my original reply which mention to use Basic syntax)? 

I forgot to add the dim i as number on the second formula.

And you did replace the test with your field?
IP IP Logged
cheryla
Groupie
Groupie


Joined: 23 Oct 2013
Online Status: Offline
Posts: 48
Quote cheryla Replybullet Posted: 31 Aug 2017 at 6:17am
Hi again.

I did replace the test with my field. I added the dim as number on the 2nd formula. So now both are saying the same thing about remaining text...

I'm not sure what basic syntax means. Sorry. I am using crystal XI if that helps at all.
IP IP Logged
kevlray
Moderator
Moderator
Avatar

Joined: 29 Oct 2009
Online Status: Offline
Posts: 1331
Quote kevlray Replybullet Posted: 31 Aug 2017 at 7:15am
In the formula editor, you can use Crystal Syntax or Basic Syntax. Look for the dropdown in the tool bar.
IP IP Logged
cheryla
Groupie
Groupie


Joined: 23 Oct 2013
Online Status: Offline
Posts: 48
Quote cheryla Replybullet Posted: 31 Aug 2017 at 8:33am
This has work wonderfully!! Thank you so much you are a lifesaver!!!
IP IP Logged
cheryla
Groupie
Groupie


Joined: 23 Oct 2013
Online Status: Offline
Posts: 48
Quote cheryla Replybullet Posted: 14 Sep 2017 at 5:49am
Hi again! As I said this formula worked perfectly. I decided to use it in yet another report that I am working on that basically has the same issues. It's not working so maybe I need to tweak it somehow? Here is a sample of the data in the field (it is also a string field):

EQI#54545-55
15266D
EQI#5206-09

again I need to pull out just the numbers. The error I'm getting this time is "the string is non-numeric".

Thanks again for your help. This formula has already proved invaluable in 4 other reports so I was hoping it would work in this but it didn't.
IP IP Logged
kevlray
Moderator
Moderator
Avatar

Joined: 29 Oct 2009
Online Status: Offline
Posts: 1331
Quote kevlray Replybullet Posted: 18 Sep 2017 at 4:57am
The example data you have presented may need to be parsed with a for loop (or something similar).  I will see if I have some time later.
IP IP Logged
Post Reply Post New Topic
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



This page was generated in 0.031 seconds.