<!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.1170" name=GENERATOR></HEAD>
<BODY>
<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></BODY></HTML>