Changing a string field on report to say the actual Month

[dmsmsa]

I have field on a report that prints out 2000101, 2000102, 2000103, ... . This is a string variable on a SYBASE database, being access by ODBC. I need to read the last two characters and put the actual month name in that field. I'm using Crystal Reports Version 7.

Thank You

 

[MalcolmW]

As usual, there a couple of ways of doing this.
In SCR V8, there is a specific function to convert an integer to a month name.
In SCR V7, you can use a formula to convert the month numeric string into an equivalent month name. This is an ugly, nested if then else, kinda like
If Right({Sybase.Datefield},2) = '01' Then
'January'
Else If Right({Sybase.Datefield},2) = '02' Then
'February'
....
etc
....
Else If Right({Sybase.Datefield},2) = '12' Then
'December'
Else
'Invalid Month'
An alternative method that actually works best with dates is just to format the date field to print only the month (right click on a date field, choose format).
Because you don't have a true date field, you will have to fake one. The year or day of month doesn't matter, so we can always use the first day of the month, year 2000.

Date(2000, ToNumber(Right({Sybase.Datefield},2),0), 1)
I'm not currently working with v7, so I'm a bit uncertain as to whether the ToNumber(x,y) function existed (I think ToNumber(x) did, but the rounding option - where y is the number of decimal places - was an undocumented but working feature of version 7).

 

[dmsmsa]

Except for the rounding option for the ToNumber, both examples worked. Now my next problem is to sort the alpha months in the proper order instead of starting with April. Also the following works to

Right({SYBASE.DATEFIELD},2)+"-"+Left({SYBAE.DATEFIELD},4)


mm-yyyy

 

[kenhamady]

Sort on the original field, and they should be in the correct order, just don't display that field on the report.

Ken Hamady

http://www.kenhamady.com/

 

[MalcolmW]

Or if you just convert the string to a date, then sort it by date, your problem is solved.
Date(ToNumber(Left({Sybase.Datefield},4)), ToNumber(Right({Sybase.Datefield},2)), 1)
Just format that to print as mm-yyyy (or whatever). The format won't alter the sorting.
I don't think this is a better solution that the one Ken suggested above, but it is nice to have a choice...