You can check the EndDate value in parameter expression, and if it's incorrect, set it to StartDate + 1 Month.
Something like:
= IIF(DateDiff(DateInterval.Month, Parameters!StartDate.Value, Parameters!EndDate.Value) = 0, Parameters!EndDate.Value, AddDate(DateInterval.Month, 1, Parameters!StartDate.Value))
If you just want notify user, you can place some hidden text box with appropriate formatting (red big font) and message about date parameters incorrect range. In Hidden expression set
= (DateDiff(DateInterval.Month, Parameters!StartDate.Value, Parameters!EndDate.Value) <> 0)
Also, you can combine both actions with custom code:
Public DateMessage As String
Public Function ValidateDate(StartDate As DateTime, EndDate As DateTime) As DateTime
Dim ResultDate As DateTime
If (DateDiff(DateInterval.Month, StartDate, EndDate) <> 0) Then
ResultDate = AddDate(DateInterval.Month, 1, StartDate)
DateMessage = String.Format("End Date parameter value {0}
was out of range and was changed to {1}", EndDate, ResultDate)
Else
ResultDate = EndDate
End If
End Function
Then, in Parameter value expression:
= Code.ValidateDate(Parameters!StartDate.Value, Parameters!EndDate.Value)
In Value property of tbDateParameterMessage textbox:
= Code.DateMessage
And in Hidden property expression:
= String.IsNullOrEmpty(Code.DateMessage)
EDIT
But if you want to stop report running, use this custom code:
Public Function CheckDate(SDate as Date, EDate as Date) as Integer
Dim msg as String
msg = ""
If (SDate > EDate) Then
msg="Start Date should not be later than End Date"
End If
If msg <> "" Then
MsgBox(msg, 16, "Parameter Validation Error")
Err.Raise(6,Report) 'Raise an overflow
End If
End Function
It's taken from SQLServerCentral forum.
Best Answer
If you're not doing so already, make sure the report accepts a date parameter (which defaults to today's date) and use that to determine which month to retrieve the data for.
The rest should be relatively easy:
First, right-click on your "Next" TextBox and bring up its properties dialog. There's a tab there called "Navigation". On the Navigation tab you can specify a report to which that TextBox should link ("Jump to report"). Choose the same report that you're currently working on.
Now click the "Parameters" button to specify the parameters you want to pass to the report you're linking to. Choose the date parameter from the list of available parameters, and for its value, use an expression like this:
(I've assumed that your parameter's name is "Date" there.)
So you're effectively linking to the same report, but adding one month to the date it's running for. Obviously the "Prev" TextBox works the same way except pass -1 to the dateadd call.
For a more ".NET" expression, you could also try this code:
I'm fairly certain that will accomplish the same thing, and might be a tad more readable.
You might also want to style those TextBoxes so that they look like hyperlinks to the end user (blue/underlined, perhaps, depending on your standards).