Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Crystal - text from a datepart

Status
Not open for further replies.

MassBarHolly

Technical User
May 11, 2011
2
US
I am relatively new to Crystal 11. I want to use the select expert to extract records where a text field called “Program Year” is equal to the year of today’s date. I have tried a bunch of things to no avail. Can anyone help?
 
use this formula to convert the text field to a date:
//{@TextToDate}
stringvar ttd;
IF isnull({YourTable.ProgramYear}) or TRIM({YourTable.ProgramYear})=""
then ttd := "00000000"
else := {YourTable.ProgramYear};
Date(val(left(ttd,4)),val(mid(ttd,5,2)),val(right(ttd,2)))

then you could use this formula in the select expert:
{@TextToDate} = currentdate
 
Can we see an example of the Program year
Is it always in the same format
 
ProgramYear is a text field in the table of Mock Trial participations.

It is always 4 characters - 2010, 2011 etc.

A school may participate in the Mock Trial program over a number of years (seperate record for each year) I am trying to pull only the records for the current year hence I have tried Table.ProgramYear = YEAR(GETDATE())- works in SQL, =YEAR(CurrentDATE), =ToText (DatePart(“yyyy”,(CurrentDate)) among other things none of which work.
 
In that case you can try Fisheromacse formula with a little change
;



use this formula to convert the text field to a date:
//{@TextToDate}
stringvar ttd;
IF isnull({YourTable.ProgramYear}) or TRIM({YourTable.ProgramYear})=""
then ttd := "0000"
else := {YourTable.ProgramYear};
Date(val(left(ttd,4)),01,01)

then you could use this formula in the select expert:
year({@TextToDate}) = year(currentdate)


Hopefully that should get you what you need



 
val({table.programyear}) = year(currentdate)

-LB
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top