Dan Rooney


Hi, I have 3 parameters on my form. StartDate (datetime), EndDate (datetime) and CompanyName(string). The default values are: StartDate (Non-queried) 1-1-2005, EndDate (Non-queried) 1-1-2008, CompanyName (From query) DataSetBelow, Value field (AccountFamily):

SELECT DISTINCT AccountFamily
FROM CallDataRecords

The table on the form contains the following DataSet:

SELECT Salutation, InboundTimeMS, OutboundTimeMS, ModifiedOn, IsRightParty, AccountFamily

FROM CallDataRecords

WHERE AccountFamily = @CompanyName
AND ModifiedOn
BETWEEN @StartDate AND @EndDate

The error I get is: "Query execution failed for data set (one directly above)".

"Must declare the scalar variable "@CompanyName".

Can anybody shed light please

Thanks, Dan




Re: Newbie Parameter Problem

VDeepak


I believe you have to declare the variable first and then use it the query..

DECLARE @CompanyName nvarchar(25)

--Initilize the declared variable

SELECT DISTINCT @CompanyName = AccountFamily
FROM CallDataRecords

-- use it

SELECT Salutation, InboundTimeMS, OutboundTimeMS, ModifiedOn, IsRightParty, AccountFamily

FROM CallDataRecords

WHERE AccountFamily = @CompanyName
AND ModifiedOn
BETWEEN @StartDate AND @EndDate

Hope this helps.....







Re: Newbie Parameter Problem

Dan Rooney

I tried that, but I got the following error:

"The report parameter 'CompanyName' uses the field 'AccountFamily' in a data set reference, but the data set 'DistinctComanyName' does not contain that field".

I also tried editing the Dataset and adding in the parameters tab of the Dataset. However that doesn;t help either ( ).






Re: Newbie Parameter Problem

Dan Rooney

Sorry, please ignore my last post. I fixed it by adding the parameters to the second dataset. (They were not defined).

Thanks!





Re: Newbie Parameter Problem

VDeepak

cool ... all the best