<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=us-ascii">
<TITLE>Message</TITLE>
<META content="MSHTML 6.00.2800.1141" name=GENERATOR></HEAD>
<BODY>
<DIV><SPAN class=631514707-07062003><FONT face=Arial color=#0000ff
size=2>Arthur,</FONT></SPAN></DIV>
<DIV><SPAN class=631514707-07062003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=631514707-07062003><FONT face=Arial color=#0000ff size=2>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?</FONT></SPAN></DIV>
<DIV><SPAN class=631514707-07062003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=631514707-07062003><FONT face=Arial color=#0000ff
size=2>Thanks</FONT></SPAN></DIV>
<DIV><SPAN class=631514707-07062003><FONT face=Arial color=#0000ff
size=2>Vlad</FONT></SPAN></DIV>
<DIV><SPAN class=631514707-07062003></SPAN> </DIV>
<DIV></DIV>
<DIV><FONT face=Tahoma size=2>-----Original Message-----<BR><B>From:</B>
accessd-bounces@databaseadvisors.com
[mailto:accessd-bounces@databaseadvisors.com] <B>On Behalf Of </B>Arthur
Fuller<BR><B>Sent:</B> Saturday, 7 June 2003 11:33 AM<BR><B>To:</B>
accessd@databaseadvisors.com<BR><B>Subject:</B> RE: [AccessD] Like Operator SQL
Syntax<BR><BR></DIV></FONT>
<BLOCKQUOTE dir=ltr style="MARGIN-RIGHT: 0px">
<DIV><SPAN class=562052201-07062003><FONT face=Arial color=#0000ff size=2>At
first blush, the first thing I see wrong is this:</FONT></SPAN></DIV>
<DIV><SPAN class=562052201-07062003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=562052201-07062003><FONT face=Arial color=#0000ff
size=2><FONT
color=#000000>
& "WHERE (((tblCustomers.CustomerName) Like % + "
_<BR> &
strInput & " + %)) " _</FONT><BR>Should be</FONT></SPAN></DIV>
<DIV><SPAN class=562052201-07062003><FONT face=Arial
size=2>
& "WHERE (((tblCustomers.CustomerName) Like "%" + "
_<BR> &
strInput & " + %)) " _</FONT><BR></SPAN></DIV>
<DIV><FONT face=Arial color=#0000ff size=2><SPAN class=562052201-07062003>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.</SPAN></FONT></DIV>
<DIV><FONT face=Arial color=#0000ff size=2><SPAN
class=562052201-07062003></SPAN></FONT> </DIV>
<DIV><FONT face=Arial color=#0000ff size=2><SPAN class=562052201-07062003>More
importantly, when I see code like this I wonder why. My take on Access is as
follows:</SPAN></FONT></DIV>
<DIV><FONT face=Arial color=#0000ff size=2><SPAN
class=562052201-07062003></SPAN></FONT> </DIV>
<DIV><FONT face=Arial color=#0000ff size=2><SPAN class=562052201-07062003>1.
Write code only when necessary. Take advantage of built-in tech whenever
possible.</SPAN></FONT></DIV>
<DIV><FONT face=Arial color=#0000ff size=2><SPAN class=562052201-07062003>2.
Write SQL rather than VBA whenever possible.</SPAN></FONT></DIV>
<DIV><FONT face=Arial color=#0000ff size=2></FONT> </DIV>
<DIV><SPAN class=562052201-07062003><FONT face=Arial color=#0000ff size=2>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.</FONT></SPAN></DIV>
<DIV><SPAN class=562052201-07062003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=562052201-07062003><FONT face=Arial color=#0000ff
size=2>HTH,</FONT></SPAN></DIV>
<DIV><SPAN class=562052201-07062003><FONT face=Arial color=#0000ff
size=2>Arthur</FONT></SPAN></DIV><!-- Converted from text/plain format -->
<P><FONT face=Tahoma size=2>-----Original Message-----<BR><B>From:</B>
accessd-bounces@databaseadvisors.com
[mailto:accessd-bounces@databaseadvisors.com]<B>On Behalf Of
</B>ACTEBS<BR><B>Sent:</B> Thursday, June 05, 2003 7:56 AM<BR><B>To:</B>
access group<BR><B>Subject:</B> [AccessD] Like Operator SQL
Syntax<BR><BR></FONT></P>
<BLOCKQUOTE dir=ltr style="MARGIN-RIGHT: 0px">
<BLOCKQUOTE dir=ltr style="MARGIN-RIGHT: 0px">
<DIV><FONT face=Arial size=2><SPAN class=531174814-05062003>Hi
People,</SPAN></FONT></DIV>
<DIV><FONT face=Arial size=2><SPAN
class=531174814-05062003></SPAN></FONT> </DIV>
<DIV><FONT face=Arial size=2><SPAN class=531174814-05062003>Can anyone
tell me what wrong with the following syntax:</SPAN></FONT></DIV>
<DIV><FONT face=Arial size=2><SPAN
class=531174814-05062003></SPAN></FONT> </DIV>
<DIV><FONT face=Arial size=2><SPAN class=531174814-05062003>strSQL =
"SELECT tblCustomers.CustomerID, "
_<BR>
& "[tblCustomers]![CustomerNumber] & '-' &
[tblCustomers]![CustomerSite] "
_<BR>
& "AS CustCode, tblCustomers.CustomerName, tblCustomers.Address1 &
' ' & "
_<BR>
& "tblCustomers.Address2 & ' ' & tblCustomers.Address3 & '
' & "
_<BR>
& "tblCustomers.Postcode AS Address, tblCustomers.DepotID FROM
tblCustomers "
_<BR>
& "WHERE (((tblCustomers.CustomerName) Like % + "
_<BR>
& strInput & " + %)) "
_<BR>
& "ORDER BY tblCustomers.CustomerName;"</SPAN></FONT></DIV>
<DIV><FONT face=Arial size=2><SPAN
class=531174814-05062003></SPAN></FONT> </DIV>
<DIV><FONT face=Arial size=2><SPAN class=531174814-05062003>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...</SPAN></FONT></DIV>
<DIV><FONT face=Arial size=2><SPAN
class=531174814-05062003></SPAN></FONT> </DIV>
<DIV><FONT face=Arial size=2><SPAN
class=531174814-05062003>Thanks</SPAN></FONT></DIV>
<DIV><FONT face=Arial size=2><SPAN
class=531174814-05062003>Vlad</SPAN></FONT></DIV>
<DIV><FONT face=Arial color=#0000ff
size=2></FONT> </DIV></BLOCKQUOTE></BLOCKQUOTE></BLOCKQUOTE></BODY></HTML>