Newbie Parameter Problem

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

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.....

|||

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 (?).

|||

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

Thanks!

|||

cool ... all the best

Newbie Datetime Parameter problem

Hi, I have setup start date and end date parameters, however when my select gets the results from between the dates, if the dates are both set to today, no results are produced. I think its because I need to add a day to the end date parameter. How can I do this?

Thanks, Dan

Dan,

What you'll need to do is add one to your end date, like you said. You can do that by updating your SQL coding to something like this:

where ....
and DateField between @.StartDate and dateadd(day, 1, @.EndDate)
...

Hope this helps.

Jarret

|||

Thats perfect.

Thank you.