[AccessD] Like Operator SQL Syntax

ADO or not, if the value is a string, you need string delimiters around
it.  Try this:

 & "WHERE (((tblCustomers.CustomerName) Like " & chr(34) & "%" _
            & strInput & "%" & chr(34) & ")) "

Of course, that assumes that you don't actually have customers with
names beginning and ending in "+".  I assumed you were trying to use +
to concatenate the wild cards, which isn't necessary.  If you were doing
something else, post back.

Charlotte Foust

-----Original Message-----
From: ACTEBS [mailto:actebs at actebs.com.au] 
Sent: Thursday, June 05, 2003 6:56 AM
To: access group
Subject: [AccessD] Like Operator SQL Syntax


Hi People,

Can anyone tell me what wrong with the following syntax:

strSQL = "SELECT tblCustomers.CustomerID, " _
            & "[tblCustomers]![CustomerNumber] & '-' &
[tblCustomers]![CustomerSite] " _
            & "AS CustCode, tblCustomers.CustomerName,
tblCustomers.Address1 & ' ' & " _
            & "tblCustomers.Address2 & ' ' & tblCustomers.Address3 & ' '
& " _
            & "tblCustomers.Postcode AS Address, tblCustomers.DepotID
FROM tblCustomers " _
            & "WHERE (((tblCustomers.CustomerName) Like % + " _
            & strInput & " + %)) " _
            & "ORDER BY tblCustomers.CustomerName;"

I am creating an ADO recordset and obviously ADO use % as the wild card
and not * so I am not sure where the issue currently is...

Thanks
Vlad