December 4, 2015 at 11:41 am
I have a master report and 25 subreports that use two user input parameters to drive them all. Works great. I want to be able to take the integer input for quarter (1, 2, 3, or 4) and convert it into the last month and last day of the quarter in the footer. So if the user input 1, I want to get 3/31 as the last day of quarter one. I've tried various things that didn't work. Here's my latest:
="Data for period 1/1/" & Parameters!Yr.Value & " through " & datepart("m",Parameters!Yr.Value) & "/" & datepart("d",Parameters!Qtr.Value)
This is the expression in a textbox in the footer. When I run the report the textbox writes #Error on the report.
I've googled a lot but I haven't found a case of someone wanting to take an integer parameter and converting it last month and day. Almost all examples use the current date with date arithmetic. I would like the flexibility of using the user input quarter for the desired report.
Thanks in advance.
December 4, 2015 at 11:51 am
Here is one way to calculate the last day of the quarter (untested):
=DateAdd(DateInterval.Day, -1, DateAdd(DateInterval.Month, 1, DateSerial(Parameters!Yr.Value, Parameters!Qtr.Value * 3, 1)))
It constructs the first day of the quarter ending month
then adds one month to get the first of the next month
and then subtracts 1 day to get the last day of the quarter
December 4, 2015 at 1:24 pm
Thanks! That worked perfectly!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply