Solved

Error when Converting String To True Date with Formula

Posted on 2006-07-20
7
306 Views
Last Modified: 2008-02-01
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)
0
Comment
Question by:anidac
7 Comments
 
LVL 17

Expert Comment

by:MIKE
ID: 17150163
Think you need to deal with NULL dates.

MikeV
0
 
LVL 100

Accepted Solution

by:
mlmcc earned 84 total points
ID: 17151598
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
 
LVL 16

Assisted Solution

by:wykabryan
wykabryan earned 83 total points
ID: 17153426
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
 

Author Comment

by:anidac
ID: 17154860
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
 
LVL 17

Assisted Solution

by:MIKE
MIKE earned 83 total points
ID: 17154909
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

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
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…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

831 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