Error when Converting String To True Date with Formula

I have a string field that contains a date that can be in the following formats:

1/1/06
1/1/2006
01/01/06
01/01/2006
====================
WhileReadingRecords;
StringVar TextString := {T1243460887.q6_3}; // place your field or parameter prompt in place of this field
 
NumberVar Slash1 := instr(TextString,"/");
NumberVar Slash2 := instr(4,TextString,"/");
 
NumberVar Yr := If Length (TextString) - Slash2 > 3
then Val (right(TextString,4))
else if Val (right(TextString,2)) < 50
                then Val (right(TextString,2))+2000
                else Val (right(TextString,2))+1900;
 
NumberVar Mth := Val (Left (TextString,Slash1-1));
 
NumberVar Dy := Val (TextString [Slash1+1 to Slash2-1]);
 
Date(Yr, Mth, Dy)
======================

I have used the following formula in the past to handle this issue but for some reason it is not working in this new report.  First it tells me that their is no error in the formula.  When I run the report I receive the following error:

"String length is less than 0 or not an integer" and highlights the following line of code:
                 NumberVar Mth := Val (Left (TextString,Slash1-1));

Any help with this problem will be greatly appriciated.  (Crystal Professional v. 10)
anidacAsked:
Who is Participating?
 
mlmccConnect With a Mentor Commented:
Agree.  Here is another method

Try this.  As I read your solution it is mm/dd/year.  Do you really need it WhileReadingRecords?


WhileReadingRecords;
StringVar  Array strDateParts[3];

if (IsNull({{T1243460887.q6_3}}) ) then
    Date(1900,1,1)  'Use any invalid date you want
else
(
    strDateParts := Split({T1243460887.q6_3},"/") ;
    Date(val(strDateParts[3]),val(strDateParts[1]),val(strDateParts[2]))
)

mlmcc


0
 
MIKESoftware Solutions ConsultantCommented:
Think you need to deal with NULL dates.

MikeV
0
 
wykabryanConnect With a Mentor Commented:
While Mlmcc will work, here is alternative to using the WhileReadingRecords, which might impact performance depending on the number of records.  

create a formula:
if isnull(table.datefield)then
date(1900,01,01) else
date(totext(date(table.datefield),"MM/dd/yyyy"))
0
 
anidacAuthor Commented:
MLMMC:

When I try your solution I receive the following error message even with all NULLS excluded from my record selection:

"A subscript must be between 1 and the size of the array"
HIGHLIGHTED ERROR LINE:  StringVar  Array strDateParts[3]

CORRECTION TO MY ORIGINAL QUESTION POST:

The field contains "-" NOT "/" if it matters.

1-1-06
1-1-2006
01-01-06
01-01-2006
0
 
MIKEConnect With a Mentor Software Solutions ConsultantCommented:
It matters:

WhileReadingRecords;
StringVar  Array strDateParts[3];

if (IsNull({{T1243460887.q6_3}}) ) then
    Date(1900,1,1)  'Use any invalid date you want
else
(
    strDateParts := Split({T1243460887.q6_3},"-") ;
    Date(val(strDateParts[3]),val(strDateParts[1]),val(strDateParts[2]))
)


MikeV
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.