Column heading on report to pull from main menu (1 Viewer)

[Angel69]

Hi,

I have fields on the main menu where folks put in a start and end date prior to running a report. I'm doing a monthly report that pulls current month, and two prior months of data. For example, the user selects a date range of 10/1/14 to 10/31/14 the report will show August, September and October results.

I need the column headings to change each month depending on the date range they select to run the report so in November they will pull 11/1/14 to 11/30/14 and get September, October and November.

The headings for the columns would then adjust.

I put an unbound text box with the formula as follows in the control source of the report page header to pull the month that is equal to the current month minus two (I'm just showing one of the column headings formula):

=Format([Forms]![Main Menu]![EndDate]-2,"mmmm yyyy")

This is not working. It's pulling the current month which I have set to run for October so it should show August 2014. Any suggestions?

TIA

 

[pbaldy]

Try the DateAdd() function. What you're doing is just subtracting 2 days.

 

[Angel69]

Great! That worked.

I used =DateAdd("m",-2,[Forms]![Main Menu]![EndDate]) to pull two months prior

and =DateAdd("m",-1,[Forms]![Main Menu]![EndDate]) to pull one month prior

and =Format([Forms]![Main Menu]![EndDate],"mmmm yyyy") for current month if anyone is interested.

 

[pbaldy]

Happy to help!