[AccessD] Like Operator SQL Syntax

Arthur,
 
Nice take on why you would go about coding issue utilising Access. The
thing is that as most people you get comfortable in how you do certain
things that have worked for you over time. I would really like to see
some of these static functions you talk about. Where is the message base
you discuss?
 
Thanks
Vlad
 
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Arthur Fuller
Sent: Saturday, 7 June 2003 11:33 AM
To: accessd at databaseadvisors.com
Subject: RE: [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/20030607/9c54d8cb/attachment-0001.html>