Rolling report parameter for the 1st day of the month boundary value for @DateFrom

Question

I have an issue the boundary values of my @datefrom parameter that runs a daily report.

This is how far I have got with it, but returns an error.

DateFrom

=IIF(DatePart("d",1,0,0),(DateAdd("m", -1, DateSerial(Year(Now), Month(now), 1))),(DateSerial(Year(Now), Month(now), 1)))

Error Message

Argument 'DateValue' cannot be converted to type 'Date'

The expression is meant to allow for the boundary date change from the last day of the month to the first of the next.

Any help would be appreciated. Thanks in advance.

So given some dates, e.g. 30-jun-2014, 01-jul-2014, 02-jul-2014, what values do you expect from the expression? — Ian Preston
30th June would return 1st - 29th, 1st July would return 1st - 30th, 2nd would return only 1st - 1st. — Pete Hicks
Are you looking for a single date being returned? If so, would a DateTo parameter/expression also be required? Or are you specifically looking for a string combining two dates as per your comment? — Ian Preston
I have found that the simple expression "DateAdd("d",-1,Today)" returns the correct value. It's only the DateFrom that I have an issue with. The expression "DateSerial(Year(Now), Month(now), 1)" works, but it failed when the month changed from the 30th to the 1st. On the 30th it returned 1st-29th and the 1st returned nothing. — Pete Hicks

Ian Preston Ian Preston · Accepted Answer · 2014-07-02T12:40:45

Assuming you only want to get one date returned in your expression, you can use something:

=IIf(DatePart("d", Today, 0, 0) = 1
    , DateAdd("m", -1,  Today)
    , DateSerial(Year(Today), Month(Today), 1))

For today (i.e. 02-Jul-2014) this will return the date 01-Jul-2014, but running it yesterday (i.e. 01-Jul-2014) you would have got 01-Jun-2014.

Your existing expression looks like it has some syntax issues - you need to compare the result of the DatePart expression to an actual integer in the first clause of the IIf expression; it expects a boolean value in the first clause.

Also, since you are only using DatePart to get the date you can use fewer parameters:

=IIf(DatePart("d", Today) = 1
    , DateAdd("m", -1,  Today)
    , DateSerial(Year(Today), Month(Today), 1))

Rolling report parameter for the 1st day of the month boundary value for @DateFrom

1 Answers