Results 1 to 8 of 8

Thread: SQL using LIKE

  1. #1
    Join Date
    Mar 2004
    Posts
    79

    Unanswered: SQL using LIKE

    I have an input field called Desciption. When the user enters text into this field, I want to query file for records that contain that text anywhere in Description field of file.

    This is what I have (not working)
    SELECT tblInvoiceDtl.[Description], tblCustomers.CustName, tblInvoiceHdr.InvoiceNumber FROM (tblInvoiceHdr LEFT JOIN tblCustomers ON tblInvoiceHdr.BillToNumber = tblCustomers.CustNumber) LEFT JOIN tblInvoicedtl ON tblInvoiceHdr.InvoiceNumber = tblInvoicedtl.InvoiceNumber WHERE (((tblInvoiceHdr.Posted) Is Null) and ((tblInvoiceDtl.Description) LIKE '*(Forms![frmInvoiceSearch]!Description)*')) ORDER BY tblInvoiceHdr.InvoiceNumber;

    Is there some other to do this?

  2. #2
    Join Date
    Jun 2004
    Location
    Florida, US
    Posts
    521
    I believe LIKE section should be something like:
    Code:
    LIKE '*' & (Forms![frmInvoiceSearch]!Description) & '*'
    ghozy.

  3. #3
    Join Date
    Mar 2004
    Posts
    79
    You are wonderful!

  4. #4
    Join Date
    Jun 2004
    Location
    Florida, US
    Posts
    521
    lol, I'm not. but thanks anyway.
    ghozy.

  5. #5
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926

    Like Statement

    You can also try using Like """ & (Forms![frmInvoiceSearch]!Description) & """ (I'm not at my computer at home so I can't remember the exact syntax, but using Like '*' & (Forms![frmInvoiceSearch]!Description) & '*' may give you some problems if there is a ' (single quote) in the text.

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    It may not be the neatest way of expressing quote marks but instead of
    Code:
    Like """ & (Forms![frmInvoiceSearch]!Description) & """
    Code:
    Like " & chr$(34) & (Forms![frmInvoiceSearch]!Description) & chr$(34)
    avoids the problems that Access can some times cause when you try to insert double quotation marks

  7. #7
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926

    chr$(34)

    Never tried that healdem. Good to know. Thanks for the tip.

  8. #8
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    use the replace command as well


    Like """ & Replace(Forms![frmInvoiceSearch]!Description," ","*") & """

    if a user put in bla bla

    the replace will replace the space with *

    so it comes bla*bla will find more.
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

Posting Permissions

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