Using LIKE in SQL

[Brian]

Hello. Sorry this is kinda long, but I don't know how
else to tell you my problem.

I want to use the LIKE command to search for records that
contain parts of words in a given field. I construct the
SQL in the query builder and get the following:

SELECT ACCOUNT_LIST.COMPLEMENTOR, ACCOUNT_LIST.ID,
ACCOUNT_LIST.ACCOUNT_NAME, REP_LIST.REP_NAME,
CS_LIST.CS_NAME, ACCOUNT_LIST.CAT,
ACCOUNT_LIST.MARKET_AREA, ACCOUNT_LIST.MAKER_LOC,
ACCOUNT_LIST.STATE, ACCOUNT_LIST.AGENCY_NAME,
ACCOUNT_LIST.PENDING, ACCOUNT_LIST.ADDED,
ACCOUNT_LIST.IHD, ACCOUNT_LIST.CODE, ACCOUNT_LIST.OUT,
ACCOUNT_LIST.REMARKS
FROM REP_LIST INNER JOIN (CS_LIST INNER JOIN ACCOUNT_LIST
ON CS_LIST.id = ACCOUNT_LIST.CS) ON REP_LIST.id =
ACCOUNT_LIST.REP
WHERE (((ACCOUNT_LIST.COMPLEMENTOR) Like "*A*"));

I want to take this and pass it through the rst.Open
method. I have named the varible containing the SQL
statement: strsearch.

The statement i have used to execute this is :
rst.Open strsearch

And strsearch is equal to:

SELECT ACCOUNT_LIST.COMPLEMENTOR, ACCOUNT_LIST.ID,
ACCOUNT_LIST.ACCOUNT_NAME, REP_LIST.REP_ID,
CS_LIST.CS_ID, ACCOUNT_LIST.CAT,
ACCOUNT_LIST.MARKET_AREA, ACCOUNT_LIST.MAKER_LOC,
ACCOUNT_LIST.STATE, ACCOUNT_LIST.AGENCY_NAME,
ACCOUNT_LIST.PENDING, ACCOUNT_LIST.ADDED,
ACCOUNT_LIST.IHD, ACCOUNT_LIST.CODE, ACCOUNT_LIST.OUT,
ACCOUNT_LIST.REMARKS, ACCOUNT_LIST.ident FROM REP_LIST
INNER JOIN (CS_LIST INNER JOIN ACCOUNT_LIST ON CS_LIST.id
= ACCOUNT_LIST.CS) ON REP_LIST.id = ACCOUNT_LIST.REP
WHERE (((ACCOUNT_LIST.COMPLEMENTOR) LIKE '*A*'))

The last line is where i am having the problem. I get a
syntax error here. Any idea how my syntax is wrong? If
i say (for the last line):

WHERE (((ACCOUNT_LIST.COMPLEMENTOR)='A'))

it gives me no syntax error, so I'm thinking the problem
is not with my use of single quotes('). Any ideas?
Thanks is advance!!

Brian

 

[Newbie]

What is the error?

 

[Brian]

oops, there is not an error. It just returns a recordset
with no records. But when I run the query in the SQL
builder, I get the correct results.

The syntax error, "Syntax error (missing operator) in
query expression '(((ACCOUNT_LIST.COMPLEMENTOR) LIKE
*'A'*))'." occurs when the last line is:
WHERE (((ACCOUNT_LIST.COMPLEMENTOR) LIKE *'A'*))

Any idea why my recordset is blank?

 

[Tim Ferguson]

WHERE (((ACCOUNT_LIST.COMPLEMENTOR) LIKE *'A'*))

Any idea why my recordset is blank?

Don't know. I'd just fix the error and get the right records anyway. Try
this:

WHERE Complementor LIKE '*A*'


The stars need to be inside the quotes. Also, remember that the wildcards
are different in ADO, so you probably need

WHERE Complementor LIKE '%A%'


Hope that helps


Tim F

 

[Brian]

Yep, I used the % and it works great! Thanks!!

Brian