Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2010
    Posts
    29

    Unanswered: Keyword search with Access 2010 FE

    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.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    If you want to search for a string value (or part of a string value) into a column, you need to pass this (sub)string as an argument to the function.

    You also need to handle the case when the (sub)string is empty (zero-length) or is Null.

    Try something like:
    Code:
    ALTER PROCEDURE dbo.[Proc_QryCon&RegKeywordSearch]
    (
          @StartDate DATETIME
        , @EndDate DATETIME
        , @SearchString NVARCHAR(50) = NULL
    )
    AS 
    DECLARE @Pattern NVARCHAR(52)
    IF ISNULL(@SearchString, '') = ''
        SET @Pattern = '%'
    ELSE   
        SET @Pattern = '%' + @SearchString + '%' 
    SELECT   PI
           , RDStudyNo
           , Waiverofconsent
           , WaiverofDocumentation
           , Consents
           , TotalSubjects
           , Ref#
           , ID
           , Findings
           , AuditResults
    FROM     [TblConsents&RegulatoryAudits]
    WHERE  ( AuditResults LIKE @pattern ) AND 
           ( Monthtoreport >= @StartDate ) AND 
           ( Monthtoreport < @EndDate );
    RETURN @@ROWCOUNT
    GO
    Note: You should remove the ampersand ('&') from the name of the stored procedure as well as from the name of the table: sooner or later you'll be in trouble because of it.
    Have a nice day!

  3. #3
    Join Date
    Nov 2010
    Posts
    29
    Thanks! Worked like a charm!!

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •