Record Selection for Past 12 months 1

[BHaines]

Crystal reports 8.5 dilemma. I'm at my wits end here. I've tried to set up a record sort to only show records from the last 12 months, but although it worked the last time I had the report open, now whenever I refresh the data it goes back to drawing records from all the way back to April of 2003. Any ideas or suggestions? Here is the sort code with the relevant part bolded and below it is the formula it is drawing from.

Sort Expert Code

(if {?NameParameter} <> "ALL" then
(trim({S_CONTACT.LAST_NAME}) & ', ' &
trim({S_CONTACT.FST_NAME}) = {?nameparameter}) else
if {?NameParameter} = "ALL" then
true
) and
(if {@Last 12 Months} = 1 then
true
) and
(if {?WhatSites} <> "ALL" then
{S_CONTACT.SOC_SECURITY_NUM}= {?WhatSites} else
if {?WhatSites} = "ALL" then
true
)


@Last 12 Months Code

IF Date({S_CUST_SURVEY.LAST_UPD}) In LastYearYTD Then 0 Else 1

 

[lbass]

LastYearYTD would select records from the previous calendar year up to the same date last year as the currentdate. So if you ran the report today, you would get records from 1/1/04 to 1/3/04. If you had run them last week, say on 12/27/04, you would have seen records from 1/1/03 to 12/27/03. So, you can see that this formula does not select the last 12 months. Were you looking for the last full 12 months? Then you would use a formula like:

{table.date} in dateserial(year(currentdate - day(currentdate)),
month(currentdate-day(currentdate))-11,01) to
date(year(currentdate - day(currentdate)),
month(currentdate-day(currentdate)),day(currentdate-day(currentdate)))

-LB

 

[BHaines]

That appears to have worked! I'll continue testing it to be sure, but it looks correct!