Select Expert Formula Based on Current Year

[DoPLeGaNgA]

Hello:

Using CR10, I'm trying to select records in a report using a formula that returns records for providers that have not had a specific screening during that year.

This is the formula I'm using:
year(CurrentDate)<>Year({checkupdate})

The problem is there are records selected from the previous year(s). How do a write a formula that will only capture results for people that didn't get a checkup for the current year which don't include records from previous years?

Any insight would be much appreciated.

 

[dgillz]

Please post sample raw data of one person who fits the criteria, and one that doesn't.

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

http://www.gainfocus.biz

"What version of URGENT!!! are you using?

 

[lbass]

You need to check the most recent service date against the current year. Do you mean calendar year? Anyway, it might be fastest to create a SQL expression {%maxdate} like the following:

(
Select max(`checkupdate`)
from table A
where A.`person` = table.`person`
)

Then you could use a record selection formula like this:

{%maxdate} < date(year(currentdate),1,1)

You could alternatively use no record selection formula, insert a group on {table.person}, and then go to report->selection formula->GROUP and enter:

maximum({table.checkupdate},{table.person}) < date(year(currentdate),1,1)

-LB

 

[DoPLeGaNgA]

Thank you all for your quick response. lbass thank you for your suggestion, but when I use a record selection formula I get a crystal error that states this must be evaluated after and when I try using the SQL Expression i get a sql error. I'll keep trying. Any other thoughts would be helpful.

 

[lbass]

The selection formula will work if you place it in the correct place: report->selection formula->[red]GROUP[/red]:

maximum({table.checkupdate},{table.person}) < date(year(currentdate),1,1)

You must insert a group on your person field first.

-LB

 

[DoPLeGaNgA]

Thank you lbass I'll give that a try.