I NEED HELP ON THIS SIMPLE SEARCH CODE.... 1

[EdRev]

I have this simple sql that searches a database table field for a particular string that is passed by an asp form

area_name=trim(request.form("area_name&quot)

strtext = "SELECT area_name.area_no, area_name.area_name, area_tel.area_tel, area_tel.area_fax, " &_
"area_address.area_add1,area_address.area_city, area_address.area_city, area_address.area_state, area_address.area_zip " &_
"FROM (area_address INNER JOIN area_name ON area_address.area_no = area_name.area_no) INNER JOIN " &_
"area_tel ON area_name.area_no = area_tel.area_no " &_
"where area_name.area_name like '*"&area_name&"*'"

This sql code doesn't work. But if I take out the * from the search criteria, it works but not as I expected. It only searches for an exact match. I want to be able to search for the string within the field.

Any help will be greatly appreciated.

 

[Guest_imported]

Try to use [red]%[/red] instead of [red]*[/red] in your [red]Like[/red] clause.

 

[EdRev]

thanks guestg,

I believe % is used with SQL database, I'm just using Access database. I tried it too and still didn't get it to work.

 

[Guest_imported]

Does it give you any error messages?

 

[EdRev]

No. I just get no matching record found.

 

[Guest_imported]

Have you tried to run this query in Access directly?
Sorry. I'm asking these questions 'cause the construct "Like '*" & something & "*'" doesn't seem to be a problem: I just tried it with some test query, and it worked fine.

 

[EdRev]

Guess what, I tried it again with % and it worked. The first time I tried it, I forgot to delete the last *.

I run it directly thru Access and it works fine, but when I translate it to my ASP, it didn't.

Anyway, thanks again. You've been a big help.

 

[Guest_imported]

One more thing. Try to use Response.Write and Response.End right after composing strtext to check to see what your query looks like and what value of area_name you're passing to the Access:

strtext = "Select ..."
response.write strtext
response.end