I have an Access project with a SQL backend. I am calling a report from a form, and the form has a field that allows the user to specify the number of weeks of history to show in a subreport on the main report (I want to go back to the first day of the weeks specified). Here is the recordsource in Access: "SELECT * FROM dbo.tblBidCINotes WHERE ((ciActionRequired = 0) OR (ciActionRequired = 1 AND ciDone = 1)) AND ciDate>='" & DATEADD("ww", -Forms!frmReportMenu!ctl_H
istWeeks, (Date - Weekday(Date) + 1)) & "'"
Unfortunately, this doesn't work with a SQL backend, so I switched to a stored procedure that receives the ctl_HistWeeks parameter. (It gets its value from a control by that name in the Report Header section of the main report.) This works beautifully -- except that the subreport now ignores the Child and Parent Link properties of the subreport and shows ALL the history records for EACH detail record of the main report.
So I added another parameter to replace the link field, and put that control (ctl_bmID) in the DETAIL section of the main report. Now the subreport is blank for all records. Any ideas short of putting all the desired history records in a temporary table? I'll post the stored procedure below.
CREATE PROCEDURE spFMHistoryRecords
-- Add the parameters for the stored procedure here
@ctl_HistWeeks int, @ctl_bmID int
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
Declare @datDate as datetime
Set @datDate = Convert(datetime, Convert(Varchar(10), GetDate(), 101))
SELECT * FROM dbo.tblBidCINotes WHERE ((ciActionRequired = 0)
OR (ciActionRequired = 1 AND ciDone = 1))
AND ciDate>=DATEADD(ww, -@ctl_HistWeeks, (@datDate - datepart(dw, @datDate) + 1))