Solved

Error when Converting String To True Date with Formula

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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

708 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

20 Experts available now in Live!

Get 1:1 Help Now