Using LIKE in SQL

  • Thread starter Thread starter Brian
  • Start date Start date
B

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
 
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?
 
WHERE (((ACCOUNT_LIST.COMPLEMENTOR) LIKE *'A'*))

Any idea why my recordset is blank?
Click to expand...

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
 
Back
Top