Passing data from subreport to main report

I need to convert a Crystal report that contains three subreports. Each of these subreports uses a different stored procedure to obtain data. The main report uses data from these subreports to calculate averages in the footer etc.

How do i get the data contained in these subreports so i can use it in my main report?

Thanks in advance

Pass the data through parameters from the subreport to the main report.

Or maybe you just need to restructure how these reports work. The main report shouldn't be dependent on the subreports. The subreports should be dependent (most likely) on the main report.

|||

You cannot reference a sub report from the main report to get totals, etc.

The only way parameters will work is if the user clicks on the subreport and opens the main report, but you are not doing that.

The way I have gotten around this is to calculate the totals/averages in a different dataset, load them into parameters (You cannot put fields into the footer) and put the value of the parameter in a textbox in the footer.

HtH

BobP

sql

pass in null/blank value in the date field or declare the field as string and co

I need to pass in null/blank value in the date field or declare the field as string and convert date back to string.

I tried the 2nd option but I am having trouble converting the two digits of the recordset (rs_get_msp_info(2), 1, 2))) into a four digit yr. But it will only the yr in two digits.
The mfg_start_date is delcared as a string variable

mfg_start_date = CStr(CDate(Mid(rs_get_msp_info(2), 3, 2) & "/" & Mid(rs_get_msp_info(2), 5, 2) & "/" & Mid(rs_get_msp_info(2), 1, 2)))

option 1
I will have to declare the mfg_start_date as date but I need to send in a blank value for this variable in the stored procedure. It won't accept a null or blank value.

With refresh_shipping_sched
.ActiveConnection = CurrentProject.Connection
.CommandText = "spRefresh_shipping_sched"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("ret_val", adInteger, adParamReturnValue)
.Parameters.Append .CreateParameter("@.option", adInteger, adParamInput, 4, update_option)
.Parameters.Append .CreateParameter("@.mfg_ord_num", adChar, adParamInput, mfg_ord_num_length, "")
.Parameters.Append .CreateParameter("@.mfg_start_date", adChar, adParamInput, 10, "")
Set rs_refresh_shipping_sched = .Execute
End

Please helpThe stored procedure will accept null if you define the parameter that way:

create procedure TESTPROCEDURE (@.TestDate datetime = NULL)
as
select @.TestDate
go

exec TESTPROCEDURE '1/1/2003'
go

exec TESTPROCEDURE
go

blindman|||I think vbNull also works when passing in a parameter. The code you have below is passing in an empty string which I'm sure I don't have to tell you is not null.

Try the following:
.Parameters.Append .CreateParameter("@.mfg_start_date", adChar, adParamInput, 10, vbNull)

I think the stored proc idea is better though, it's safer and better for your data integrity.

Dan|||Originally posted by danielacroft
I think vbNull also works when passing in a parameter. The code you have below is passing in an empty string which I'm sure I don't have to tell you is not null.

Try the following:
.Parameters.Append .CreateParameter("@.mfg_start_date", adChar, adParamInput, 10, vbNull)

I think the stored proc idea is better though, it's safer and better for your data integrity.

Dan

Hello Dan,

What I need is an empty string in the date field to pass in in the stored procedure. What is the vb code for that?

Thanks!|||The code to pass null (empty string won't work and null will only work if you have allowed nulls on this column in your db design) for a parameter is this:

.Parameters.Append .CreateParameter("@.mfg_start_date", adChar, adParamInput, 10, vbNull)

I modified your existing code. I'm not 100% sure that this will work but it should.

Dan|||Originally posted by danielacroft
The code to pass null (empty string won't work and null will only work if you have allowed nulls on this column in your db design) for a parameter is this:

.Parameters.Append .CreateParameter("@.mfg_start_date", adChar, adParamInput, 10, vbNull)

I modified your existing code. I'm not 100% sure that this will work but it should.

Dan

Thanks for replying so quickly.
I edited my code as you have it above.
I'm stilll having trouble getting the date displaying correctly. I need the year to display in four digits. It displays something '12/31/03'
This is code that I have

Function get_date(mfg_start_date as string,..)
mfg_start_date = Mid(rs_get_msp_info(2), 3, 2) & "/" & Mid(rs_get_msp_info(2), 5, 2) & "/" & Mid(rs_get_msp_info(2), 1, 2)
mfg_start_date = CStr(Mid(rs_get_msp_info(2), 3, 2) & "/" & Mid(rs_get_msp_info(2), 5, 2) & "/" & Year(mfg_start_date))

mfg_start_date is the textbox I need the date field to display but it will only eight digits of the year and place two empty strings after. I can't understand why. In the db design the mfg_start_date field is a char with length 10 as in the stored procedure.

Also there must be a better way to write the code that I have above.

Thank you again.|||The date format is normally determined by the locale settings ont he server when you're using VB. Can I ask why you're not using a date field in your database?

Dan