Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Crystal Reports - How to extract data from a data string

Posted on 2011-03-07
29
941 Views
Last Modified: 2012-08-13
I have a database column which is a varchar string.  In it, the user stores an alpha string which (somewhere in the string) contains a date (i.e. 03-07-11).  I need to be able to extract the date part of the string to a string variable and use this on a report.
 An example of two of the strings are '03-07-11 DAVISON COMPANY' and 'WORTH VILLAGE 03-03-11.'  In example one, I want to extract '03-07-11' and in example two, I need to extract '03-03-11.
I appreciate your help.
0
Comment
Question by:mbroad02
  • 16
  • 11
  • +1
29 Comments
 
LVL 1

Expert Comment

by:dbradley626
ID: 35060172
If it is for SqlClient you can use the SqlConnectionStringBuilder class

 string spliter
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 35060270
Can there be other -'s or numbers in the string?

Can there be strings that look like but aren't dates like

'WORTH 83-14-99 VILLAGE 03-03-11'

mlmcc
0
 
LVL 1

Expert Comment

by:Hath1ASC
ID: 35060435
You would need to write a custom function in the Crystal Formula Editor, using your own custom logic and some crazy kind of combination of Crystal functions such as InStr, Mid, NumericText (to test the values between the hyphens returned from Mid), or Split and various Crystal Array functions.

Alternatively, if you are using an advanced database, you may be able to use regular expressions or other methods to extract these values into the view that Crystal uses as your datasource.  (We use Oracle Enterprise, and I would probably solve your issue by coding a PL/SQL function that uses some REGEXP_SUBSTR call combined with some error-handling custom logic, returning NULL if unable to extract a single valid date or datestring.
0
Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

Author Comment

by:mbroad02
ID: 35060588
I am restricted to using a formula in Crystal as my means for extracting the data.  No, there are not otherr numbers in the filed which may resemble a date.  The only contents are an alpha name and the date.   How about we start with idea for a formula which can extract a date at the beginning and end of a string and go from there??

thanks
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 35060832
My questions were to clarify the issue.  So lon gas the - is only in the date and all dates have the format as NN-NN-NN
Try this formula

Local StringVar strField := {YourField};
Local NumberVar nbrHyphenLoc;

nbrHyphenLoc := InStr(strField,'-');
If nbrHyphenLoc > 0 then
   Mid(strField,nbrHyphenLoc-2,8)
else
  ""

mlmcc
0
 

Author Comment

by:mbroad02
ID: 35070907
This worked GREAT!!  I have one other related question.   After I extract the date , I am finding that users have entered the date in three possible formats: MM-DD-YYYY , MM-DD-YY or YY-MM-DD.   I have handled the first two formats just fine, but HOW do I change YY-MM-DD to MM-DD-YY?
Your help is GREATLY appreciated.
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 35072614
How can you tell the format it is in?

11-03-09  -  9 mar 2011,  3 sep 2011, 11 mar 2009, 3 nov 2009

mlmcc
0
 

Author Comment

by:mbroad02
ID: 35087251
The format is 11-03-09
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 35087450
But how do I know what that means?  It is a valid date and can be interpreted in any of the 4 ways I mentioned.  Actually there are 2 more but that would mean the middle value would be the year which in general doesn't make any sense.

WHat I am getting at is if you have a date that someone entered wrong how can you tell it is wrong?  I cant tell if 11-03-09 is year 2009 or 2011

If all dates are supposed to be in 2011 then the 11 field is the year and it could be handled that way.

mlmcc
0
 

Author Comment

by:mbroad02
ID: 35094706
The date format is YY-MM-DD.    I need to be able to change YY-MM-DD to MM-DD-YY.  The first two are ALWAY the year in this case.
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 35096499
You indicated above that users are entering data in 3 formats.  Assuming you can somehow differentiate between the formats.  If it is in YY-MM-DD the following will change it

Local StringVar Array DateParts;
DateParts := Split({YourDateFormula},'-');
DateParts [2] & '-' & DateParts[3] & '-' & DateParts[1]

mlmcc
0
 

Author Comment

by:mbroad02
ID: 35098599
I will give it a try.
0
 

Author Comment

by:mbroad02
ID: 35110070
What is YourDateFormula ??  is this the field containing the backwards date (i.e. 11-01-13)??  Please advise.

thanks
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 35113419
YourFormula is just the datefield you are trying to change.  Yes, the field with the backwards date.

mlmcc
0
 

Author Comment

by:mbroad02
ID: 35129143
Ok, we are getting really close to a conclusion here.  
Here is the formula I created from your answer (displayField2 is my backwards date):

Global StringVar displayField2;
Global StringVar displayField3;


Local StringVar Array DateParts;
DateParts := Split(displayField2,'-');
displayField3 := DateParts[2] & '-' & DateParts[3] & '-' & DateParts[1]


Small issue is that I am getting the following error in the formula:
"A subscript must be between 1 and the size of the array" and is it referencing the first DateParts in the above formula.Any suggestions?
thanks
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 35129354
Do you have NULL fields?

Could some of the data not be in the correct format?


Global StringVar displayField2;
Global StringVar displayField3;
Local StringVar Array DateParts;
DateParts := Split(displayField2,'-');
If UBound(DateParts) <> 3 then
   "Bad Data"
Else
    displayField3 := DateParts[2] & '-' & DateParts[3] & '-' & DateParts[1]

mlmcc
0
 

Author Comment

by:mbroad02
ID: 35131579
I see why I had nulls in the field and have corrected it.  Now I have another issue.  The result field now has one of two values:  either a date format of MM-DD-YYYY or a date format of YY-MM-DD.  How can I analyze this field to determine WHICH format I am looking at (so I know if I have to reformat it per your array formaula)??
In other words, is there a formula I could use which would look at this variable field and determine if the field is MM-DD-YYYY or YY-MM-DD?  
The most telltale difference that I can see is that from the 7th position on, the field would eith contain 2 characters (DD) or 4 characters (YYYY).  Any suggestions?

thanks
0
 
LVL 100

Accepted Solution

by:
mlmcc earned 500 total points
ID: 35131772
If they have different formats then you could fix it in a formula

Global StringVar displayField2;
Global StringVar displayField3;
Local StringVar Array DateParts;
DateParts := Split(displayField2,'-');
If UBound(DateParts) = 3 then
     If Val(DateParts[3] > 999) then
          displayField3 := DateParts[1] & '-' & DateParts[2] & '-' & Right(DateParts[3],2)
     Else
         displayField3 := DateParts[2] & '-' & DateParts[3] & '-' & DateParts[1]
Else
    displayField3  := "";

That should give you "" for bad data and NULL and MM-DD-YY for good dates

mlmcc
0
 

Author Comment

by:mbroad02
ID: 35131839
Awesome!  thank you.  I will try it and get back to you tomorrow.
0
 

Author Comment

by:mbroad02
ID: 35137601
I think that this may solve my issue.  Just one small thing:  
when I add your code to my formula, I get the error "a string is required here" in the line  If Val(DateParts[3] > 999) then


Suggestion?
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 35139126
Where do you set displayField2?  You are splitting it so it needs to be the date field.

mlmcc
0
 

Author Comment

by:mbroad02
ID: 35139264
i changed the first formula (which populates the date field) to use a field called displayField instead of displayField2.
The formula which takes the date field out of that to get in proper print format contains:
Global StringVar displayField;
Global StringVar displayField2;
Local StringVar Array DateParts;
DateParts := Split(displayField,'-');
If UBound(DateParts) = 3 then
     If Val(DateParts[3] > 999) then
          displayField2 := DateParts[1] & '-' & DateParts[2] & '-' & Right(DateParts[3],2)
     Else
         displayField2 := DateParts[2] & '-' & DateParts[3] & '-' & DateParts[1]
Else
    displayField2  := "";
displayField2;
0
 

Author Comment

by:mbroad02
ID: 35139304
This is the first formula, which populates displayField.  It pulls a user-enetered date out of a text comment field.
Global StringVar strField := {service.dscr};
Global NumberVar nbrHyphenLoc := 0;
Global StringVar resultField;
Global StringVar displayField;
Global StringVar Array DateParts;
Global NumberVar nbrHyphenLoc := 0;

nbrHyphenLoc := InStr(strField,'-');
If nbrHyphenLoc > 0 then
   resultField := Mid(strField,nbrHyphenLoc-2,10);


nbrHyphenLoc := InStr(resultField,'C');
If nbrHyphenLoc > 0 then
    displayField := left(resultField,8)
else
    displayField := resultField;
displayField;
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 35139402
Change this -  ) is in the wrong place

 If Val(DateParts[3] > 999) then

to
 If Val(DateParts[3]) > 999 then

mlmcc
0
 

Author Comment

by:mbroad02
ID: 35140968
OK, almost there.   Check out the attachment.  I have it coded like above (I beleive) but I get NO result.
Date-formula-sample-data.doc
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 35141447
Where on the report are the formulas?

Try this
Add WhilePrintingRecords; as the first line of formula 1

Add  EvaluateAfter({@Formula1}); as the first line of formula2

mlmcc
Database4.mdb
Database4Report.rpt
0
 

Author Comment

by:mbroad02
ID: 35146721
Both formulas are on the group footer where all of the print line detail is located.  Perhaps I need to consolidate formula 2 into the end of formula 1.
Thanks
0
 

Author Comment

by:mbroad02
ID: 35146764
It's a thing of beauty!!   I moved Formula 2 to the end of Formula 1 and....IT WORKED!!  Thank you so very much for all of your help!
0
 

Author Closing Comment

by:mbroad02
ID: 35146768
Excellent work!  Very patient and detailed in your help!!  thanks again!
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

792 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question