have date field but for current year?

[rrmcguire]

Hello everyone,

I have a date field called, anniversary_date, which Im trying to generate the totals of another field total_comm, between that date and the current date, in this case 12/9/10, but for the anniversary_date, I need that to be for the current year.

i.e. person A has anniversary date of 1/17/07, but for my range I need the date to be 1/17/10 - 12/9/10, then find my total of total_comm

any ideas?

thanks

 

[fisheromacse]

what do you need to happen if the anniversary month/day is after the current month/day?

ie: anniv. date = 12/12/2009 and current date is 12/9/2010

i have not tested this, but maybe start with:
dateserial(year(currentdate),month({table.anniversary_date),day({table.anniversary_date})

 

[lbass]

I think you'd have to do something like this:

if date(year(currentdate),month({table.annivdate}), day({table.annivdate})) >= currentdate then
(
if {table.salesdate} in
in date(year(currentdate)-1,month({table.annivdate}), day({table.annivdate})) to currentdate then
{table.salesamount}
) else
if date(year(currentdate),month({table.annivdate}), day({table.annivdate})) < currentdate then
(
if {table.salesdate} in
in dateserial(year(currentdate),month({table.annivdate}), day({table.annivdate})) to currentdate then
{table.salesamount}
)

-LB

 

[rrmcguire]

to use the anniversary date in the current year set to a value, so then I could get my totals based on the date range of that field between the current date, how would I go about doing that

i.e. if the anniversary date is 5/15/06, but I need 5/15/10 through 12/9/10, how would I represent the date calculated to get 5/15/10 as a value

 

[lbass]

if date(year(currentdate),month({table.annivdate}), day({table.annivdate})) >= currentdate then
date(year(currentdate)-1,month({table.annivdate}), day({table.annivdate})) else
if date(year(currentdate),month({table.annivdate}), day({table.annivdate})) < currentdate then
date(year(currentdate),month({table.annivdate}), day({table.annivdate}))

-LB