Problem with Passing Parameters to the Subreport 1

[elinsd]

Hi,
I have a subreport requires input parameters (FromDate and ToDate)to run the underlying SQL server stored procedure. The main report has nothing to do with the subreport except they share the same input parameters (same date range).
The subreport's query runs fine by itself and it is catching the null value and return a string properly when there is no data. However, when I ran the main report with the subreport, after puting in the desired date range, the main report printed out fine but I got an error message from the subreport query saying "Invalid length parameter passed to the substring function". My guess is that the substring function was using a field that contains null value, but when I checked the data, it was not the case. I'd appreciate if anyone can help.

 

[elinsd]

I found the source of this problem through MS knowledgebase. It is a known problem when working with Access front-end and SQL backend that the use of Substring function violates Access rules so it prevented my subreport from running. My workaround was to replace the Substring function with a Right and a Left function to manipulate my string and it worked fine. (Luckly, I only had to format the leading and trailing characters in the string. For other types of string manipulation, it may not be so easy.)

Another thing that I also found out about passing parameters to a subreport was to drop an invisible text box on the main report if the "Link Master Field" isn't already on the main report (for example, an input parameter) otherwise the subreport won't get the parameter passed prperly. Hope this helps.