Subtracting 1 year from a single Parameter that prompts for a date range

[Former Member]

I have a single parameter that prompts the user for a date range.

I can display the values from the prompt, but I'd also like to display the values for the same period one year in the past.

For instance if the user enters 1/1/2014 to 5/30/2014 I want to display 1/1/2013 to 5/30/2013.

Thanks for the help.

W

[JWiseman · Accepted solution]

hi Wade,

try something like this in your Report > Selection Formula > Record editor...substituting the fields and parameters for your own...

{Orders.Order Date} in {?DateRange}

or

{Orders.Order Date} in dateadd('yyyy', -1, minimum({?DateRange})) to dateadd('yyyy', -1, maximum({?DateRange}))

the above doesn't really handle leap years in the calc, but you'd need to specify exactly what kind of logic you'd want to use when a leap year comes into play.

you could also use something like this

{Orders.Order Date} in {?DateRange}

or

(

{Orders.Order Date} >= date(year(minimum({?DateRange}))-1, month(minimum({?DateRange})), day(minimum({?DateRange}))) and

{Orders.Order Date} <= date(year(maximum({?DateRange}))-1, month(maximum({?DateRange})), day(maximum({?DateRange})))

)

check the Database menu > Show SQL Query to see how these two are processed, especially with leap years...i.e. enter 02/29/2016 as the start date to your parameter. you'll need to figure out how you want to handle these exceptions.

[Former Member · Accepted solution]

Thanks!

That didn't work exactly,  but led to create this which is exactly what I wanter!

CDate(DateAdd ("yyyy",-1 , Minimum({?Reporting Period}))) & "-" & CDate(DateAdd ("yyyy", -1, Maximum ({?Reporting Period})))

[JWiseman · Accepted solution]

ahh...sorry...i thought that you wanted an actual record selection / filter vs. just displaying the range.