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,928

    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,928

    chr$(34)

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

  8. #8
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,482
    Provided Answers: 11
    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

    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-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.
    DONT WORRY ABOUT THOSE WHO TALK BEHIND YOUR BACK
    THEY'RE BEHIND YOU FOR A REASON

Posting Permissions

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