ToText/ToNumber not working for number field 1

[genekr]

I have a field that is a number field and is displayed 20,051,101.00 which is a date 20051101. I need to take just the 5th and 6th char's to be able to sort by Month.

I've tried (ToText(mid({APDISFIL.AP_DIST_DATE},5,2))) but get error "A string is required here" and it highlights {APDISFIL.AP_DIST_DATE}.

I've also tried (ToNumber(mid({APDISFIL.AP_DIST_DATE},5,2))) but get same error msg. I'm assuming it is because the field is a number, because this same formula worked for a string field.

Any help is appreciated.
Gene

 

[genekr]

I've tried using

Whileprintingrecords;
stringvar MyDate:=totext({APDISFIL.AP_DIST_DATE},0,"");
val(mid(MyDate,5,2))

and it seems to sort correctly, but I also need to group by the month and when I try it the @month formula, which I uesed above, does not show up a a group option.

 

[lbass]

Convert it entirely to a date:

stringvar strDate:=totext({APDISFIL.AP_DIST_DATE},0,"");
date(val(left(strDate,4)),val(mid(strDate,5,2)), val(right(strDate,2)))

Then you can insert a group on this formula and you will have the option to print on change of month.

-LB

 

[dgillz]

Convert it to a date:

NumberToDate(YYYYMMDD)

If this function is not available in your version of crystal under additional functions, it is available as a downloadable UFL.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"A fine is a tax for doing wrong. A tax is a fine for doing well.
" - unknown

 

[genekr]

Awesome! Thank you very much.

 

[synapsevampire]

Converting it to a date is best, and you could even do this on the database using a SQL Expression, which is the fastest means.

To address why your formulas didn't work:

(ToText(mid({APDISFIL.AP_DIST_DATE},5,2)))

You tried using MID against a number, if you had reversed it and converted it to text first you would have been OK:

(ToText(mid({APDISFIL.AP_DIST_DATE},5,2)))

should be:

val(mid(ToText({APDISFIL.AP_DIST_DATE},0,""),5,2))

The second formula also incorrectly tried using a MID against a numeric, but then corrently converting it to a numeric.

Those are important function to understand, and you were close, but you need to convert to a string PRIOR to using a string function against a field.

-k