[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1016
  • Last Modified:

Crystal Reports - How to extract data from a data string

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
mbroad02
Asked:
mbroad02
  • 16
  • 11
  • +1
1 Solution
 
dbradley626Commented:
If it is for SqlClient you can use the SqlConnectionStringBuilder class

 string spliter
0
 
mlmccCommented:
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
 
Hath1ASCCommented:
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: 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.

 
mbroad02Author Commented:
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
 
mlmccCommented:
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
 
mbroad02Author Commented:
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
 
mlmccCommented:
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
 
mbroad02Author Commented:
The format is 11-03-09
0
 
mlmccCommented:
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
 
mbroad02Author Commented:
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
 
mlmccCommented:
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
 
mbroad02Author Commented:
I will give it a try.
0
 
mbroad02Author Commented:
What is YourDateFormula ??  is this the field containing the backwards date (i.e. 11-01-13)??  Please advise.

thanks
0
 
mlmccCommented:
YourFormula is just the datefield you are trying to change.  Yes, the field with the backwards date.

mlmcc
0
 
mbroad02Author Commented:
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
 
mlmccCommented:
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
 
mbroad02Author Commented:
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
 
mlmccCommented:
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
 
mbroad02Author Commented:
Awesome!  thank you.  I will try it and get back to you tomorrow.
0
 
mbroad02Author Commented:
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
 
mlmccCommented:
Where do you set displayField2?  You are splitting it so it needs to be the date field.

mlmcc
0
 
mbroad02Author Commented:
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
 
mbroad02Author Commented:
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
 
mlmccCommented:
Change this -  ) is in the wrong place

 If Val(DateParts[3] > 999) then

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

mlmcc
0
 
mbroad02Author Commented:
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
 
mlmccCommented:
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
 
mbroad02Author Commented:
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
 
mbroad02Author Commented:
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
 
mbroad02Author Commented:
Excellent work!  Very patient and detailed in your help!!  thanks again!
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

  • 16
  • 11
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now