Good evening.
I am working on a report to match catalog mailings to the number of orders received and the dollar amounts of those orders. The report draws on a query; the query is composed of two other querys, each of which draws on three tables and another query that summarizes data from three smaller tables. Several of the fields in the main query are calculated.
Some of the fields are supposed to show all the recent data. Others are calculated to display totals that exclude information from the last six months. For instance, if today's date is in the month of May, the calculation should screen out records from the first five months of the year, plus the month of December in the previous year. If todays' date is in July, the calculation should screen out records more recent than January of this year.
I have created a formula to do this sort of calculation. If I need to make changes or there are improvements you can suggest it would be helpful. However, my biggest concern is that, so far, the fields that should screen out recent data have not yet displayed ANY information for me to put on a report.
The formula is as follows:
Sum(
IIf(
(Year(Date())=Year([MAIL_DATE]) And Month([MAIL_DATE])+5<Month(Date()))
Or
(Year(Date())=Year([MAIL_DATE])+1 And Month([MAIL_DATE])<6+Month(Date()))
Or
(Year(Date())>Year([MAIL_DATE])+1),[Orders],0)
)
Thank you in advance for your help. (This forum has been an exemplary resource.)
I am working on a report to match catalog mailings to the number of orders received and the dollar amounts of those orders. The report draws on a query; the query is composed of two other querys, each of which draws on three tables and another query that summarizes data from three smaller tables. Several of the fields in the main query are calculated.
Some of the fields are supposed to show all the recent data. Others are calculated to display totals that exclude information from the last six months. For instance, if today's date is in the month of May, the calculation should screen out records from the first five months of the year, plus the month of December in the previous year. If todays' date is in July, the calculation should screen out records more recent than January of this year.
I have created a formula to do this sort of calculation. If I need to make changes or there are improvements you can suggest it would be helpful. However, my biggest concern is that, so far, the fields that should screen out recent data have not yet displayed ANY information for me to put on a report.
The formula is as follows:
Sum(
IIf(
(Year(Date())=Year([MAIL_DATE]) And Month([MAIL_DATE])+5<Month(Date()))
Or
(Year(Date())=Year([MAIL_DATE])+1 And Month([MAIL_DATE])<6+Month(Date()))
Or
(Year(Date())>Year([MAIL_DATE])+1),[Orders],0)
)
Thank you in advance for your help. (This forum has been an exemplary resource.)
