This code produces a table fine, however its when I try to pull data from it in Access is when I get the problem. The error I receive in access is: Insufficient number of arguments were supplied for the procedure or function.
ALTER FUNCTION dbo.R114ReportC opy (@Acyear smallint)
RETURNS @RtnTab table
(DeptName varchar(100),Ac year smallint,
Eid smallint,FacNam e varchar(50),
FormName varchar(100), FormNo smallint,
IndN smallint, DivN smallint, DepN smallint,
IndAvCont float, IndSDCont float,
IndAvVal float, IndSDVal float,
DivAvCont float, DivSDCont float,
DivAvVal float, DivSDVal float,
DepAvCont float, DepSDCont float,
DepAvVal float, DepSDVal float,
LIndAvCont float, LIndSDCont float,
LIndAvVal float, LIndSDVal float, LIndN smallint,
LDivAvCont float, LDivSDCont float,
LDivAvVal float, LDivSDVal float, LDivN smallint,
LDepAvCont float, LDepSDCont float,
LDepAvVal float, LDepSDVal float, LDepN smallint,
CIndAvCont float, CIndSDCont float,
CIndAvVal float, CIndSDVal float, CIndN smallint,
CDivAvCont float, CDivSDCont float,
CDivAvVal float, CDivSDVal float, CDivN smallint,
CDepAvCont float, CDepSDCont float,
CDepAvVal float, CDepSDVal float, CDepN smallint,
Comments text)
AS
BEGIN
Declare @LastYear smallint
Set @Lastyear=@Acye ar-1
Insert into @RtnTab
Select dbo.deptcodenam e(dept) Department,Num. *, comments
from
(
Select @acyear acyear,T.*,
Lavcontact,Lsdc ontact,Lavvalue ,Lsdvalue,Ln,
Ldvcontactav,Ld vcontactsd,Ldvv alueav,Ldvvalue sd,Ldvn,
Ldepcontactav,L depcontactsd,Ld epvalueav,Ldepv aluesd,Ldepn,
Cavcontact,Csdc ontact,Cavvalue ,Csdvalue,Cn,
Cdvcontactav,Cd vcontactsd,Cdvv alueav,Cdvvalue sd,Cdvn,
Cdepcontactav,C depcontactsd,Cd epvalueav,Cdepv aluesd,Cdepn
from
(
Select eid,dbo.idtonam e(eid) Name,descriptio n formname,f.form no,
N,DvN,DepN,
avcontact,sdcon tact,AvValue,Sd Value,
dvcontactAV,dvc ontactSD,dvValu eAv,dvValueSD,
depContactAV,de pContactSD,depV alueAv,DepValue SD from R114ByYear(@Acy ear,@Acyear) R, R114FormName F
where R.formno=F.form no
) T left join
(
Select eid,formno,
avcontact Lavcontact,sdco ntact Lsdcontact,avva lue Lavvalue,sdvalu e Lsdvalue,n Ln,
dvcontactav Ldvcontactav,dv contactsd Ldvcontactsd,dv valueav Ldvvalueav,dvva luesd Ldvvaluesd,dvn Ldvn,
depcontactav Ldepcontactav,d epcontactsd Ldepcontactsd,d epvalueav Ldepvalueav,dep valuesd Ldepvaluesd,dep n Ldepn
from R114ByYear(@Las tyear,@Lastyear ) L
) L on T.eid=L.eid and T.formno=L.form no
left join
(
Select eid,formno,
avcontact Cavcontact,sdco ntact Csdcontact,avva lue Cavvalue,sdvalu e Csdvalue,n Cn,
dvcontactav Cdvcontactav,dv contactsd Cdvcontactsd,dv valueav Cdvvalueav,dvva luesd Cdvvaluesd,dvn Cdvn,
depcontactav Cdepcontactav,d epcontactsd Cdepcontactsd,d epvalueav Cdepvalueav,dep valuesd Cdepvaluesd,dep n Cdepn
from R114ByYear(1995 ,@Acyear) C
) C on T.eid=C.eid and T.formno=c.form no
) Num Left join
(
Select eid,formtype formno, comments from R114
where acadyear=@Acyea r
and (comments is not null
or cast(comments as varchar(10)) not in ('NULL','NA','n one','n/a','n.a',' ') )
) comment on Num.eid=comment .eid and Num.formno=comm ent.formno
,RVUMasterList M
where
M.acyear=@acyea r
and Num.eid=M.eid
order by 1,4,5
Return
END
ALTER FUNCTION dbo.R114ReportC opy (@Acyear smallint)
RETURNS @RtnTab table
(DeptName varchar(100),Ac year smallint,
Eid smallint,FacNam e varchar(50),
FormName varchar(100), FormNo smallint,
IndN smallint, DivN smallint, DepN smallint,
IndAvCont float, IndSDCont float,
IndAvVal float, IndSDVal float,
DivAvCont float, DivSDCont float,
DivAvVal float, DivSDVal float,
DepAvCont float, DepSDCont float,
DepAvVal float, DepSDVal float,
LIndAvCont float, LIndSDCont float,
LIndAvVal float, LIndSDVal float, LIndN smallint,
LDivAvCont float, LDivSDCont float,
LDivAvVal float, LDivSDVal float, LDivN smallint,
LDepAvCont float, LDepSDCont float,
LDepAvVal float, LDepSDVal float, LDepN smallint,
CIndAvCont float, CIndSDCont float,
CIndAvVal float, CIndSDVal float, CIndN smallint,
CDivAvCont float, CDivSDCont float,
CDivAvVal float, CDivSDVal float, CDivN smallint,
CDepAvCont float, CDepSDCont float,
CDepAvVal float, CDepSDVal float, CDepN smallint,
Comments text)
AS
BEGIN
Declare @LastYear smallint
Set @Lastyear=@Acye ar-1
Insert into @RtnTab
Select dbo.deptcodenam e(dept) Department,Num. *, comments
from
(
Select @acyear acyear,T.*,
Lavcontact,Lsdc ontact,Lavvalue ,Lsdvalue,Ln,
Ldvcontactav,Ld vcontactsd,Ldvv alueav,Ldvvalue sd,Ldvn,
Ldepcontactav,L depcontactsd,Ld epvalueav,Ldepv aluesd,Ldepn,
Cavcontact,Csdc ontact,Cavvalue ,Csdvalue,Cn,
Cdvcontactav,Cd vcontactsd,Cdvv alueav,Cdvvalue sd,Cdvn,
Cdepcontactav,C depcontactsd,Cd epvalueav,Cdepv aluesd,Cdepn
from
(
Select eid,dbo.idtonam e(eid) Name,descriptio n formname,f.form no,
N,DvN,DepN,
avcontact,sdcon tact,AvValue,Sd Value,
dvcontactAV,dvc ontactSD,dvValu eAv,dvValueSD,
depContactAV,de pContactSD,depV alueAv,DepValue SD from R114ByYear(@Acy ear,@Acyear) R, R114FormName F
where R.formno=F.form no
) T left join
(
Select eid,formno,
avcontact Lavcontact,sdco ntact Lsdcontact,avva lue Lavvalue,sdvalu e Lsdvalue,n Ln,
dvcontactav Ldvcontactav,dv contactsd Ldvcontactsd,dv valueav Ldvvalueav,dvva luesd Ldvvaluesd,dvn Ldvn,
depcontactav Ldepcontactav,d epcontactsd Ldepcontactsd,d epvalueav Ldepvalueav,dep valuesd Ldepvaluesd,dep n Ldepn
from R114ByYear(@Las tyear,@Lastyear ) L
) L on T.eid=L.eid and T.formno=L.form no
left join
(
Select eid,formno,
avcontact Cavcontact,sdco ntact Csdcontact,avva lue Cavvalue,sdvalu e Csdvalue,n Cn,
dvcontactav Cdvcontactav,dv contactsd Cdvcontactsd,dv valueav Cdvvalueav,dvva luesd Cdvvaluesd,dvn Cdvn,
depcontactav Cdepcontactav,d epcontactsd Cdepcontactsd,d epvalueav Cdepvalueav,dep valuesd Cdepvaluesd,dep n Cdepn
from R114ByYear(1995 ,@Acyear) C
) C on T.eid=C.eid and T.formno=c.form no
) Num Left join
(
Select eid,formtype formno, comments from R114
where acadyear=@Acyea r
and (comments is not null
or cast(comments as varchar(10)) not in ('NULL','NA','n one','n/a','n.a',' ') )
) comment on Num.eid=comment .eid and Num.formno=comm ent.formno
,RVUMasterList M
where
M.acyear=@acyea r
and Num.eid=M.eid
order by 1,4,5
Return
END
Comment