Parameters from A2K Project to SQL 7

[BobJacksonNCI]

Goal is to pass dates from Access form controls through to SQL 7 backend for use within a stored procedure. First, I want to get the SQL end working, and have one stored procedure executing a second dynamic dynamic stored procedure.

Executing the following results in error message:
Must declare the variable '@begindate'

Note that commenting out that line results in a query that runs as desired.

Alter Procedure MgrTB_spr_Time_Off_Show_New_PP
AS
declare @parmStatus as varchar(100), @begindate datetime, @enddate datetime
set @parmStatus = '''1'',''2'',''3'',''4'''
set @BeginDate = '2/01/04'
set @EndDate = '4/30/04'
EXEC rjackson.MgrTB_spr_Time_Off_Show_Parm @parmStatus, @BeginDate, @EndDate
RETURN

Alter PROCEDURE MgrTB_spr_Time_Off_Show_Parm
(
@parmStatus varchar (100) = '1',
@BeginDate datetime = '1/1/1900',
@EndDate datetime = '12/31/9999'
)
AS

DECLARE @sSQL varchar(8000)

SELECT @sSQL = 'SELECT'
SELECT @sSQL = @sSQL + ' T_O.FormID_One, T_O.FormStatus, T_O.EmpLastName, T_O.EmpLglFirstName,'
SELECT @sSQL = @sSQL + ' T_O.EmpMidName, T_O.Dept, T_O.DateOfForm, T_O.DtCreated,'
SELECT @sSQL = @sSQL + ' substring(T_O.Submitted_By, CHARINDEX(''\'',T_O.Submitted_By)+1,1) + '' '' +'
SELECT @sSQL = @sSQL + ' substring(T_O.Submitted_By, CHARINDEX(''\'',T_O.Submitted_By)+2,19) AS Submitted_By'
SELECT @sSQL = @sSQL + ' FROM'
SELECT @sSQL = @sSQL + ' dbo.MgrTB_tbl_Time_Off T_O'
SELECT @sSQL = @sSQL + ' WHERE'
SELECT @sSQL = @sSQL +' cast(T_O.FormStatus as char) in (' + @parmStatus + ')'
SELECT @sSQL = @sSQL + ' AND T_O.DateOfForm between @begindate and @enddate'
SELECT @sSQL = @sSQL + ' ORDER BY'
SELECT @sSQL = @sSQL + ' T_O.EmpLastName,'
SELECT @sSQL = @sSQL + ' T_O.EmpLglFirstName,'
SELECT @sSQL = @sSQL + ' T_O.EmpMidName,'
SELECT @sSQL = @sSQL + ' T_O.DateOfForm DESC'

EXEC (@sSQL)


Once the dates work, the next step is to pass the dates from Forms!fmnu_HR_MENU.txtPP_Beg_Date and
Forms!fmnu_HR_MENU.txtPP_End_Date passed along to be loaded into @begindate and @enddate.

Thanks for assisting!
Bob