Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Like SQL

Discussion in 'Information Technology' started by PeterM, Nov 16, 2005.

Tags:
  1. PeterM

    PeterM
    Expand Collapse
    Guest

    I need to search a memo field and find all records with a specific date
    somewhere in the comments...search_date below is defined as a "date"

    Dim temp_search_date As String
    temp_search_date = search_date
    Set leads = dbCDMi.OpenRecordset _
    ("SELECT comments from leads_cdmi where comments like %" & temp_search_date
    & "%", dbOpenForwardOnly)

    why doesn't this work? I get an error message saying

    3075
    Syntax error in query expression 'comments like %11/14/2005%

    thanks in advance!
     
  2. Loading...

    Similar Threads Forum Date
    Microsoft Brings 64-Bit Perks in SQL Server 2000 Service Pack (Ziff Davis) Interfaith Dialogues May 7, 2005

  3. Van T. Dinh

    Van T. Dinh
    Expand Collapse
    Guest

    Try:

    Set leads = dbCDMi.OpenRecordset _
    ("SELECT comments from leads_cdmi where comments like ""%" &
    temp_search_date
    & "%""", dbOpenForwardOnly)

    --
    HTH
    Van T. Dinh
    MVP (Access)



    "PeterM" <PeterM@discussions.microsoft.com> wrote in message
    news:EA476BA5-CFA0-47B0-97A4-BA735985D73F@microsoft.com...
    >I need to search a memo field and find all records with a specific date
    > somewhere in the comments...search_date below is defined as a "date"
    >
    > Dim temp_search_date As String
    > temp_search_date = search_date
    > Set leads = dbCDMi.OpenRecordset _
    > ("SELECT comments from leads_cdmi where comments like %" &
    > temp_search_date
    > & "%", dbOpenForwardOnly)
    >
    > why doesn't this work? I get an error message saying
    >
    > 3075
    > Syntax error in query expression 'comments like %11/14/2005%
    >
    > thanks in advance!
    >
     
  4. PeterM

    PeterM
    Expand Collapse
    Guest

    THANK YOU! THANK YOU! THANK YOU!

    "Van T. Dinh" wrote:

    > Try:
    >
    > Set leads = dbCDMi.OpenRecordset _
    > ("SELECT comments from leads_cdmi where comments like ""%" &
    > temp_search_date
    > & "%""", dbOpenForwardOnly)
    >
    > --
    > HTH
    > Van T. Dinh
    > MVP (Access)
    >
    >
    >
    > "PeterM" <PeterM@discussions.microsoft.com> wrote in message
    > news:EA476BA5-CFA0-47B0-97A4-BA735985D73F@microsoft.com...
    > >I need to search a memo field and find all records with a specific date
    > > somewhere in the comments...search_date below is defined as a "date"
    > >
    > > Dim temp_search_date As String
    > > temp_search_date = search_date
    > > Set leads = dbCDMi.OpenRecordset _
    > > ("SELECT comments from leads_cdmi where comments like %" &
    > > temp_search_date
    > > & "%", dbOpenForwardOnly)
    > >
    > > why doesn't this work? I get an error message saying
    > >
    > > 3075
    > > Syntax error in query expression 'comments like %11/14/2005%
    > >
    > > thanks in advance!
    > >

    >
    >
    >
     
  5. John Vinson

    John Vinson
    Expand Collapse
    Guest

    On Tue, 15 Nov 2005 17:09:05 -0800, PeterM
    <PeterM@discussions.microsoft.com> wrote:

    >I need to search a memo field and find all records with a specific date
    >somewhere in the comments...search_date below is defined as a "date"
    >
    >Dim temp_search_date As String
    >temp_search_date = search_date
    >Set leads = dbCDMi.OpenRecordset _
    >("SELECT comments from leads_cdmi where comments like %" & temp_search_date
    >& "%", dbOpenForwardOnly)
    >
    >why doesn't this work? I get an error message saying
    >
    >3075
    >Syntax error in query expression 'comments like %11/14/2005%
    >
    >thanks in advance!


    The wildcard character in JET databases is * rather than %; and you
    need quotemarks around the string you're searching for:

    "SELECT comments from leads_cdmi where comments like '*" &
    temp_search_date & "*'", dbOpenForwardOnly

    so it will parse to

    comments like '*11/14/2005*'

    Note that if you have variably formatted dates in your memo field
    (e.g. sometimes 8/10/2005 and sometimes 08/10/2005) you may miss data.

    John W. Vinson[MVP]
     

Share This Page