Solved

Error when Converting String To True Date with Formula

Posted on 2006-07-20
7
305 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

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…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

863 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

Need Help in Real-Time?

Connect with top rated Experts

27 Experts available now in Live!

Get 1:1 Help Now