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;
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.
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
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.