[AccessD] Like Operator SQL Syntax

At first blush, the first thing I see wrong is this:
 
            & "WHERE (((tblCustomers.CustomerName) Like % + " _
            & strInput & " + %)) " _
Should be
            & "WHERE (((tblCustomers.CustomerName) Like "%" + " _
            & strInput & " + %)) " _

But there are problems with this too. The % wildcard assumes a SQL back end.
You might want to push this out into a Const and even a compiler directive,
for maximum portability.
 
More importantly, when I see code like this I wonder why. My take on Access
is as follows:
 
1. Write code only when necessary. Take advantage of built-in tech whenever
possible.
2. Write SQL rather than VBA whenever possible.
 
>From this I conclude that dynamic SQL constructions are almost always the
wrong approach. You could instead create a static function that returns your
LIKE contents and save a query that references said static function. (If you
are unacquainted with static functions, search the message base for same.)
Rather than construct the SQL as you show in your code, save that to a named
query that invokes the static function you need (let's say for example that
it is CurrentEmployee()). The query calls the function and shows you the
three rows belonging to the current employee. Given that, all you need to do
in code is reset the current employee. IMO a much more elegant solution than
rebuilding the SQL statement every time.
 
HTH,
Arthur

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of ACTEBS
Sent: Thursday, June 05, 2003 7: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
 

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://databaseadvisors.com/pipermail/accessd/attachments/20030606/8523b1b0/attachment-0001.html>