How to use instr() in a query string ???? select * from AB where instr(ab,

nate6896 used Ask the Experts™
I'm building a Windows Applicationt to run my business.  And I have recently decided to change the way that I search my database of students.  I previously search just by the first or last name and I would like to be able to search by part of the name.  I have used instr before and when a value greater then 0 is returned then that means it is in the string I'm searching.  But I can't figure out why this string doesn't work.

adoPrimaryRS.Open "Select * from StudentINFO where instr(name_first," & "'" & varSearch & "'" & " > 1 order by Name_last", xxxxxxxxxxxxxxxxxxxxxx

Can anyone help me figure this out.
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
I've not worked much with ADO but here is what it should look like using dao

adoPrimaryRS.Open "Select * from StudentINFO where ((instr(1, name_first," &  varSearch  & ")) > 0) order by Name_last", xxxxxxxxxxxxxxxxxxxxxx

Here's another option:

adoPrimaryRS.Open "Select * from StudentINFO where ((name_first) Like '*" & varSearch & "*') order by Name_last", xxxxxxxxxxxxxxxxxxxxxx
Use the LIKE operator of SQL

Select * from StudentINFO where name_first LIKE *" &  varSearch  & "*)) > 0) order by Name_last

Cheers, Andrew


adoprimaryrs.Open "Select * from Students where ((instr(1, name_first,'" & Searchmat & "')) > 0) order by Name_last", db, adOpenStatic, adLockOptimistic

great answer just forgot the ' before the variable and the ' after the variable thansks.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial