Highlight dates in current month but prior year

[RepRider]

Working with CR 8.5 and SQL Server 2000

I am looking to automate the underlining of dates that display in a monthly report that match the current month of the report run but occur in the year prior.

Our current formula which someone has to remember to change manually each month is:
IF {Psychosocial_View.assessment_date} >= Date (2006,4,01) And {Psychosocial_View.assessment_date} <= Date (2006,4,30) Then crSingleLine

This underlines the {Psychosocial_View.assessment_date} occurring in April 2006 when the report is run at the beginning of April 2007 and indicates a new assessment is needed during the current month (April).

How can this formula be changed to automate the underlining based on the month of the report run (Datadate)?

Any help would be appreciated!

 

[RoknRole]

Hi - in the formula for the bottom border of the {assessment_date} field, change the formula to:

if {assessment_date} >= Date(Year(CurrentDate)-1, Month(CurrentDate), 01)
and {assessment_date} < Date(Year(CurrentDate)-1, Month(CurrentDate)+1, 01)
then crSingleLine else crNoLine

I think this formula will also include days on the 31st of each month, which your formula does not appear to account for.

 

[RepRider]

Thanks! I will put this in and see how it does.

When the person manually changed the date each she would include all the days of that particular month. This will help me avoid that manual change!!

 

[RepRider]

It worked beautifully!!

Thank you, thank you, thank you!!!

Can you explain what the +1 and 01 are representing so I understand better to write the next one on my own?

 

[RoknRole]

The +1 increments the Month value by 1 month, the 01 represents the first day of the month, and the -1 decreases the Year value by 1 year.