Set date parameters with Stored Procedures

[Brian62]

I need to set date parameters within Stored Procedures using a sql 2008 R2, with an access 2007 front end. The procedure needs to allow me to set parameters for a start date and an end date.

 

[pbaldy]

Since you've referenced another thread, perhaps you could clarify what you need that wasn't addressed there.

 

[Brian62]

I want to in my Access FE using a stored Procedure to select a date range like I use to do using the Between clause in a Query. For example: a popup screen would ak me start date and end date and I would end the date range I want. I hope that helps.

This is what I have so far:

SELECT StudyID,PI,RDOfficeStudyNo, RDInitialApprovalDate, ActiveStudy, RDClosureDate
FROM tblCommitteeInfoEntry
WHERE (((RDInitialApprovalDate)>12/31/2007)And (((RDInitialApprovalDate)>12/31/2007)AND (ActiveStudy) = NULL AND (RDClosureDate)>12/31/2007) OR (RDInitialApprovalDate) Between 01/01/08 And 05/31/10)
ORDER BY PI, RDOfficeStudyNo;

 

[pbaldy]

Your simplest solution is linked tables and keeping the query in Access. Have the user enter the dates on a form and let the query get them from there. To have it happen in a stored procedure, you'd have input parameters which you'd then have to provide. The SP would look like:

CREATE PROCEDURE [procPostingLog]

@FmDate as datetime,
@ToDate as datetime,
@CoCode as nvarchar(5)

AS

SELECT * 
FROM tblPostingLog
WHERE DOR_Date Between @FmDate and @ToDate and co_code = @CoCode

And you could call it like

Exec procPostingLog '2/10/12', 2/15/12', 'ABC'

You could also use pass-through queries, ADO command objects, etc, all of which are more complicated than linked tables and Access parameter queries.