Passing default parameters

I have created some reports for our school district and would like to make a
2nd parameters default to what matches the 1st parameter in a drop down list.
Example:
If a teacher selects their school from the parameter drop down listing,
what I am trying to do is so that the teacher does not have to type in their
name and ensure the sysntex is correct, but to have the 2nd parameter default
to all teachers in that school so they could choose from the listing.
I've tried using the school and the teachers in a seperate dataset but when
I then select on the school it shows the school multiple times for each
teacher. I just want the school to appear for it's parameter then the 2nd
parameter would be populated with the teachers names based on the school
selected.
Thanks in Advance
--
Wayne HessThe second parameter should be based on a dataset that accepts the school
and returns the list of teachers.
select distinct firstname + ' ' + lastname as label, teacherid as value from
from schoolteachertable where school = @.SchoolParam
Use this as the source for the second parameter.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Wayne" <Wayne@.discussions.microsoft.com> wrote in message
news:9CB415ED-7050-4073-BACD-836545F0F0E6@.microsoft.com...
>I have created some reports for our school district and would like to make
>a
> 2nd parameters default to what matches the 1st parameter in a drop down
> list.
> Example:
> If a teacher selects their school from the parameter drop down listing,
> what I am trying to do is so that the teacher does not have to type in
> their
> name and ensure the sysntex is correct, but to have the 2nd parameter
> default
> to all teachers in that school so they could choose from the listing.
> I've tried using the school and the teachers in a seperate dataset but
> when
> I then select on the school it shows the school multiple times for each
> teacher. I just want the school to appear for it's parameter then the 2nd
> parameter would be populated with the teachers names based on the school
> selected.
> Thanks in Advance
> --
> Wayne Hess

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

pass hidden parameter from one dataset to another

I have 2 datasets in my report and I want to pass a field from the 1st dataset as a parameter to the 2nd dataset.

How can I do this?

I tried to set the report parameter from a query using the 1st dataset but the parameter is always equal to the first record in the recordset-it never changes per row.

I used a subreport and passed it a parameter based on each row to get this to work.