problem with report parameters in nested IIf

I have the following to diplay the frequecny a report is run for -
based on parameters selected at run time...
=3D"Report Period " & IIf(Parameters!StartDate.Value > "01/01/1900",
Parameters!StartDate.Value & " to " & IIf(Parameters!EndDate.Value >
"01/01/1900", Parameters!EndDate.Value,
Parameters!StartDate.Value.AddHours(1)), Parameters!Frequency.Label)
but if I leave the start and end date null I get an error #Error and
the following...
The value expression for the textbox 'textbox1' contains an error:
Object variable or With block variable not set.
However, if I replace the above with the following (instead of printing
out the date parameter value in the inner iif, print out a string
instead)....
=3D"Report Period " & IIf(Parameters!StartDate.Value > "01/01/1900",
Parameters!StartDate.Value & " to " & IIf(Parameters!EndDate.Value >
"01/01/1900", "Parameters!EndDate.Value",
"Parameters!StartDate.Value.AddHours(1)"), Parameters!Frequency.Label)
it works fine and will display the frequency if both dates are null.
Any idea why this is?
Also as an aside - is there a better way to check is a date field is
null instead of > "01/01/1900"'
Thanks in advance,
Gear=F3idThis occurs for 2 reasons:
1. an Iif statement always evaluates all parts of an equation.
Therefore, if 1/2 of the statement is invalid, the whole thing throws an
error.
2. You're trying to AddHours(1) to a null value, which throws an error.
I don't know why putting it in "" causes it to work, though.
As for a better option than > "01/01/1900", I recommend >
DateTime.MinValue, as that is the default value for an unassigned DateTime.
Ciao,
Noah
Gearoid wrote:
> I have the following to diplay the frequecny a report is run for -
> based on parameters selected at run time...
> ="Report Period " & IIf(Parameters!StartDate.Value > "01/01/1900",
> Parameters!StartDate.Value & " to " & IIf(Parameters!EndDate.Value >
> "01/01/1900", Parameters!EndDate.Value,
> Parameters!StartDate.Value.AddHours(1)), Parameters!Frequency.Label)
> but if I leave the start and end date null I get an error #Error and
> the following...
> The value expression for the textbox 'textbox1' contains an error:
> Object variable or With block variable not set.
> However, if I replace the above with the following (instead of printing
> out the date parameter value in the inner iif, print out a string
> instead)....
> ="Report Period " & IIf(Parameters!StartDate.Value > "01/01/1900",
> Parameters!StartDate.Value & " to " & IIf(Parameters!EndDate.Value >
> "01/01/1900", "Parameters!EndDate.Value",
> "Parameters!StartDate.Value.AddHours(1)"), Parameters!Frequency.Label)
> it works fine and will display the frequency if both dates are null.
> Any idea why this is?
> Also as an aside - is there a better way to check is a date field is
> null instead of > "01/01/1900"'
> Thanks in advance,
> Gearóid
>|||Hey Noah,
Thanks for getting back to me on this. I put it aside for a while but
have to get it sorted now.
I reckon you're right about it trying to add 1 hour to a null value and
bombing out. But it seems kinda crazy that it would try to evaluate
all parts of an IIf statement. How would I conditionally add an hour
to a datetime value so if it's not null? I thought that's what an if
statements for?!...