Date Parameter

Hello ........

I have a DateTime parameter in my report.

I want the last date of the current month selected as default in the date parameter(datetime picker ).

Can anybody help me ?

Thanks

Hi ecn i,

You could write an expression\custom code block to set the parameter default value to be the last day of the current month.

|||

Thanks for your reply.

Please give me the example (expression) for finding the last date of the current month

and set it as default.

|||=DateSerial(Year(Now()),Month(Now()) + 2,0)
Just change the 2 to 1 to return the end date of the current month.
The report parameter was set to datetime when I used this.
|||

Thank You Very Much For Your Reply .

It Really help me a lot.

|||Hi...What is the best way to get the month and date integers for the last date of the current month?|||

To get the last day of this month, get the first date of next month, then subtract one day from it.

This expression gets the first day of next month:

Code Snippet

=CDate(Year(DateAdd("M", 1, Now)) & "/" & Month(DateAdd("M", 1, Now)) & "/" & "1")

Note that i create the date string in ISO format so that there is no confusion between days and months. Next we subtract a day:

Code Snippet

=DateAdd("d", -1, CDate(Year(DateAdd("M", 1, Now)) & "/" & Month(DateAdd("M", 1, Now)) & "/" & "1"))

This gives us a date representing the last day of this month. Then you can just surround that with a Format statement to output it as a day and month (just change the formatting string if you want month/day ordering or a different delimiter):

Code Snippet

=Format(DateAdd("d", -1, CDate(Year(DateAdd("M", 1, Now)) & "/" & Month(DateAdd("M", 1, Now)) & "/" & "1")), "dd/MM")

If you want to avoid a Format statement then you can use the Month and DatePart functions:

Code Snippet

=Month(Now) & "/" & DatePart("d", DateAdd("d", -1, CDate(Year(DateAdd("M", 1, Now)) & "/" & Month(DateAdd("M", 1, Now)) & "/" & "1")))

Of course, where i have done a CDate() on an assembled string, you can just use DateSerial(year, month, day) to achieve the same thing - i just used a string to make what was happening even more obvious.

|||

Thanks! This is just what I needed. I needed month then date with leading zeros...like 0531 for May 31 without the slash. I just tweaked the "dd/MM" to "MMdd" and all is good.

Code Snippet

=Format(DateAdd("d", -1, CDate(Year(DateAdd("M", 1, Now)) & "/" & Month(DateAdd("M", 1, Now)) & "/" & "1")), "MMdd")