Solved

Error when Converting String To True Date with Formula

Posted on 2006-07-20
7
310 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 17

Expert Comment

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

MikeV
0
 
LVL 101

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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…
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 …
If you're a developer or IT admin, you’re probably tasked with managing multiple websites, servers, applications, and levels of security on a daily basis. While this can be extremely time consuming, it can also be frustrating when systems aren't wor…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

729 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