Changing Record Source from Query to SP

[Drew]

I am trying to convert an MDB to ADP and have gotten hung up on a report.
The report has the Record Source property set to the following,

SELECT DISTINCTROW [ClassesDueQuery].[Title], [ClassesDueQuery].[DueDate],
[ClassesDueQuery].[EmployeeNo], [ClassesDueQuery].[StaffLName],
[ClassesDueQuery].[StaffFName], [ClassesTaughtTable].[ClassNo] FROM
([ClassesDueQuery] INNER JOIN [ClassesTaughtTable] ON
[ClassesDueQuery].[ClassNo] =[ClassesTaughtTable].[ClassNo]);

The problem is that the ClassesDueQuery was upsized to a Function, so when I
try to run the report I get the following message,

Invalid SQL Statement. Check the server filter on the form record source.

My function looks like this,

SELECT dbo.ClassesTaughtTable.ClassNo, dbo.ClassesTaughtTable.Title,
dbo.ClassesTaughtTable.DueDate, dbo.TrainingTable.EmployeeNo, E.EmpLName,
E.EmpFName, E.EmpActive
FROM EmpCore.dbo.tblEmployee E RIGHT OUTER JOIN
dbo.TrainingTable RIGHT OUTER JOIN
dbo.ClassesTaughtTable ON dbo.TrainingTable.ClassNo =
dbo.ClassesTaughtTable.ClassNo ON E.EmpID = dbo.TrainingTable.EmployeeNo
WHERE (dbo.ClassesTaughtTable.DueDate BETWEEN @Start_Date_ AND
@End_Date_) AND (E.EmpActive = 1)

How can I get this to work on an ADP? I am using Access 2002 on a Windows
XP SP2 machine.

Thanks,
Drew Laing

 

[Sylvain Lafontaine]

The list of parameters is missing from your function.

For the record source, you should use something like:

"Select C.Title, C.DueDate, .... From ClassesDueQuery ('" & Start_Date & "',
'" & End_Date & "') as C "

In T-SQL, dates are delimited by single quotes ' and not double quotes " or
#; hence the use of ' in the above statement. Start_Date and End_Date are
local variables to your report and must contain the dates formated as
strings in a proper way.

You can also try to use parameters but I think that you must rewrite your
function as a stored procedure. I don't know if you can call directly a
function with parameters from ADP; hence the use of the Select statement
above. With a stored procedure, this is not necessary.