Multiple Select Case operators in the same field

[MattRichardsUK]

I have a field in a table that is made up of 7 Digits made up to represent a date.
eg.
1001212 = 12 Dec 2000
the first 3 digits are for the year but 100 = 2000, 101 = 2001 and so on.
the next two are the month and the next the day

I,m trying to write a formula to convert the field into a recogniseable format for report purposes.

eg 1001212 into 12 Dec 2000

I have written the following formula but when I try and check it seagate throws back an error message saying that the remaining text does not appear to be part of the formula
is this because I have to do this kind of conversion in seperate formulas. or is there an obvious error that I have over looked.

Mattrichardsuk

Select TempYearStore[2 to 3]
Case "00" : Years := 2000
Case "01" : Years := 2001
Case "02" : Years := 2002
Case "03" : Years := 2003
Case "04" : Years := 2004
Case "05" : Years := 2005
Case "06" : Years := 2006
Case "07" : Years := 2007
Case "08" : Years := 2008
Case "09" : Years := 2009
Case &quot;10&quot; : Years := 2010; <<Formula bombs out here
Select TempMonthStore[4 to 5]
Case &quot;01&quot; : Months := 01
Case &quot;02&quot; : Months := 02
Case &quot;03&quot; : Months := 03
Case &quot;04&quot; : Months := 04
Case &quot;05&quot; : Months := 05
Case &quot;06&quot; : Months := 06
Case &quot;07&quot; : Months := 07
Case &quot;08&quot; : Months := 08
Case &quot;09&quot; : Months := 09
Case &quot;10&quot; : Months := 10
Case &quot;11&quot; : Months := 11
Case &quot;12&quot; : Months := 12;
Select TempDayStore[6 to 7]
Case &quot;01&quot; : Days := 01
Case &quot;02&quot; : Days := 02
Case &quot;03&quot; : Days := 03
Case &quot;04&quot; : Days := 04
Case &quot;05&quot; : Days := 05
Case &quot;06&quot; : Days := 06
Case &quot;07&quot; : Days := 07
Case &quot;08&quot; : Days := 08
Case &quot;09&quot; : Days := 09
Case &quot;10&quot; : Days := 10
Case &quot;11&quot; : Days := 11
Case &quot;12&quot; : Days := 12
Case &quot;13&quot; : Days := 13
Case &quot;14&quot; : Days := 14
Case &quot;15&quot; : Days := 15
Case &quot;16&quot; : Days := 16
Case &quot;17&quot; : Days := 17
Case &quot;18&quot; : Days := 18
Case &quot;19&quot; : Days := 19
Case &quot;20&quot; : Days := 20
Case &quot;21&quot; : Days := 21
Case &quot;22&quot; : Days := 22
Case &quot;23&quot; : Days := 23
Case &quot;24&quot; : Days := 24
Case &quot;25&quot; : Days := 25
Case &quot;26&quot; : Days := 26
Case &quot;27&quot; : Days := 27
Case &quot;28&quot; : Days := 28
Case &quot;29&quot; : Days := 29
Case &quot;30&quot; : Days := 30
Case &quot;31&quot; : Days := 31;


Date (Years,Months,Days);

 

[bburnell]

I'm not sure why CR does it, but it only seems to allow one select statement in a formula. I would suggest using 3 seperate formula fields (1 each for y/m/d) and bringing them back together for the report.
Brett Please visit my websites!

http://smsforce.virtualave.net

http://www.b2ksolutions.net

http://www.goodguyz.com

 

[MalcolmW]

The Select statement requires a End Select. I'm not familiar with the syntax you are using, but I usually do my Case statements in ordinary SQL. You can search for Select Statements in the Help get some syntax examples.

You may wish to consider converting the text to numbers, rather than using case statements - it may be quicker, it is more concise.
WhilePrintingRecords ;
Local NumberVar Days := ToNumber(TempDayStore[6 to 7]) ;
Local NumberVar Months := ToNumber(TempMonthStore[4 to 5]) ;
Local NumberVar Years := 2000 + ToNumber(TempYearStore[2 to 3]) ;
Date(Years, Months, Days) ;
Malcolm
Remember, if it wasn't for electricity, we'd be surfing the net by candlelight.

 

[MattRichardsUK]

Thanks m99 + Malcolm,

The Data in the field is actualy stored as a 12Character long string.

eg: 1,001,214.00
This now means that the Month value is seperated by a &quot;,&quot;.

My current formula is as follows but it doesn't seem to work.
An error message is displayed saying. 'The string is non-numeric'
Any Ideas,

WhilePrintingRecords ;
StringVar TempDayStore;
StringVar TempMonthStore;
StringVar TempYearStore;
StringVar TmpMonth;

TempDayStore:= (Totext({Daily_Calldetail_WAT.orig_date}));
TempMonthStore:= (ToText({Daily_Calldetail_WAT.orig_date}));
TempYearStore := (ToText({Daily_Calldetail_WAT.orig_date}));

WhilePrintingRecords ;
Local NumberVar Days := ToNumber(TempDayStore[8 to 9]) ;
Local NumberVar Months := ToNumber(TempMonthStore[5]);
Local NumberVar Monthsb := ToNumber(TempMonthStore[7]);
Local NumberVar Years := 2000 + ToNumber(TempYearStore[3 to 4]) ;

TmpMonth := ToText(Months) & ToText(Monthsb);
Months := ToNumber(TmpMonth);

Date(Years,Months,Days);

mattrichardsuk

 

[FowlerConsulting]

If you can use a MS SQL stored procedure I would. Steven Fowler, Principal
steve.fowler@fowlerconsulting.com

http://www.fowlerconsulting.com

- Development, Training, and Consulting

 

[bburnell]

Have you tried a replace first. You could replace the , and . with a null space, then you could manipulate the data from there.
Brett Please visit my websites!

http://smsforce.virtualave.net

http://www.b2ksolutions.net

http://www.goodguyz.com

 

[MalcolmW]

Try
ToText({Daily_Calldetail_WAT.orig_date},'####')
This is version dependent - you didn't say what version you were using (12 lashes!).
Otherwise, you can avoid the text conversion by using truncate, as in:

Local NumberVar Days := ({Daily_Calldetail_WAT.orig_date}) -Truncate({Daily_Calldetail_WAT.orig_date}, -2) ;
Local NumberVar Months := (Truncate({Daily_Calldetail_WAT.orig_date}, -2) - Truncate({Daily_Calldetail_WAT.orig_date}, -4))/100 ;
Local NumberVar Years := 1900 + (Truncate({Daily_Calldetail_WAT.orig_date}, -4)/10000 ;
Date(Years, Months, Days) ;
Malcolm
Remember, if it wasn't for electricity, we'd be surfing the net by candlelight.