Keyword search with Access 2010 FE

[Brian62]

I am trying to create a stored procedure that will allow me to search column named "AuditResults". As the StartDate and EndDate popsup to enter my date range, I would like the keyword search to popup as well so I can enter the keyword I am looking for in the "AuditResults" column. This is what I have but can't get it to work.

ALTER PROCEDURE dbo.[Proc_QryCon&RegKeywordSearch]
(@StartDate datetime,
@EndDate datetime)
AS SELECT PI, RDStudyNo, Waiverofconsent, WaiverofDocumentation, Consents, TotalSubjects, Ref#, ID, Findings, AuditResults
FROM dbo.[TblConsents&RegulatoryAudits]
WHERE (AuditResults LIKE '%') AND (Monthtoreport >= @StartDate) AND (Monthtoreport < @EndDate)

I am using SQL 2008 R2 and Access 2010.

 

[mdlueck]

I am trying to create a stored procedure that will allow me to search column named "AuditResults". As the StartDate and EndDate popsup to enter my date range, I would like the keyword search to popup as well so I can enter the keyword I am looking for in the "AuditResults" column.

????? Stored Procedures are SQL code stored on the BE DB and are executed there. There is no facility to "popup" something on the screen to prompt you to specify parameters to the Stored Procedure WITHIN the Stored Procedure code.

So I am a bit lost trying to understand the question you posted.

 

[Brian62]

I am building the procedure on the Access side. Even if I build it on the SQL side, it should work when I run the report drawing on the procedure for the information through Access. Should be no different than the StartDate and EndDate popups. Maybe I need to Delcare it, but not sure how.

 

[SQL_Hell]

ALTER PROCEDURE dbo.[Proc_QryCon&RegKeywordSearch]
(@StartDate datetime,
@EndDate datetime,
@Keyword varchar(50))
AS 
SELECT 
PI, 
RDStudyNo, 
Waiverofconsent, 
WaiverofDocumentation, 
Consents, 
TotalSubjects, 
Ref#, ID, 
Findings, 
AuditResults
FROM 
dbo.[TblConsents&RegulatoryAudits]
WHERE 
(AuditResults LIKE '%' + @Keyword + '%') 
AND (Monthtoreport >= @StartDate) 
AND (Monthtoreport < @EndDate)

 

[Brian62]

Your awesome! Worked like a charm!!!

 

[SQL_Hell]

????? Stored Procedures are SQL code stored on the BE DB and are executed there. There is no facility to "popup" something on the screen to prompt you to specify parameters to the Stored Procedure WITHIN the Stored Procedure code.

So I am a bit lost trying to understand the question you posted.

When a parameterised Stored Procedure is executed from access, it does prompt you for the input parameters unless they are being handled in code, you can test this by making the record source of a form a stored procedure and opening the form (in access project).

 

[mdlueck]

When a parameterised Stored Procedure is executed from access

However this is the SQL Server group, thus I was considering Stored Procedure in SQL Server context.

More importantly...

Your awesome! Worked like a charm!!!

All's well that end's well.