Auto Underline all dates 2 months from report run month

[RepRider]

I received help with this previously but that thread is now closed. I needed to underline all annual assessment dates in a report that were in due during the next month.
The report is run at the first of the month (such as November) but the annual assessment dates that occur during December would be underlined. The formula that someone here gave us was:

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

This was utilized by the x+2 for bottom border through the "format field" after right clicking on the field.

We were using CR 8.5 then but now have upgraded to CRXI and SQL database.

We have since decided that we want the second month after the current month to be underlined so if the report is run at the beginning of November, all annual assessment dates that occur during January would be underlined through the formula so I changed the month area to be +2.

This change creates an error message that says "A month number should be between 1 and 12."

What adjustment to the above formula should I make so that the underlined dates are for the second month after the report run month? Or is there a better way?

Any help is appreciated!!!

 

[lbass]

Use dateserial() instead of date() and you should be okay.

-LB

 

[Madawc]

You could get the same result with a much simpler formula, something like

if Datediff("m", {Psychosocial_View.assessment_date}, currentdate) > 1 
or Datediff("m", {Psychosocial_View.assessment_date}, currentdate) < -2

Something like that.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[RepRider]

I did try the dateserial noted by lbass and it worked! Many, many thanks. I learn more with each read around the forum. I am planning to add this forum to my Christmas list.
Mainly, I wanted a formula that wouldn't have to be adjusted each month. Now that it's changed and it works, what would be the reason it DIDN'T work before? Was it looking at January as 13 or something?

Madawc, I will keep your idea in mind and try something with it. Because the date we want underlined is actually a year ago I am thinking yours would underline too many? This is an prompt that the next annual assessment is due.

Thanks everyone!!!

 

[Madawc]

You could have a range like in [-1, 0, 1].

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[lbass]

Yes, January = month 13 unless you use dateserial, which accommodates the calculation.

-LB