Search with Like

[Jagjit]

Hi

I have written this statement but it is not working . i have item code like diA12thd , dabd12th so on . I am using MS-Access & i have written this statement in Visual Basic 6
esql = "SELECT * FROM m_item WHERE icode like '12%'"

Openrstitem (esql)

Thanks

[DataMiser]

What is Openrsitem ?

What do you mean when you say it is not working, Are you getting a result? An error? Did it kill the cat?

[Doogle]

I'm guessing no items were returned. You need a wild character at the front and back:

esql = "SELECT * FROM m_item WHERE icode like '%12%'"

[Jagjit]

Hi

Openrstitem is

strsql = "select * from m_itemmaster order by icode"
Openrstitem (strsql)

Thanks

[Jagjit]

Hi Doogle


when i am writing this with variable it's not working , what is wrong in this.

strsql = "SELECT * FROM m_itemmaster WHERE icode LIKE '%'" & Trim(LCase(strsearch)) & "'%'"

Your statement is working

Thanks

[DataMiser]

That is because you placed single quotes around the % signs which is not the same
You would be using
esql = "SELECT * FROM m_item WHERE icode like '%'12'%'"
instead of
esql = "SELECT * FROM m_item WHERE icode like '%12%'"

Hi

Openrstitem is

strsql = "select * from m_itemmaster order by icode"
Openrstitem (strsql)

Thanks

I asked what Openrsitem is all you did was repeated what was in your first post we can only guess what this Openrsitem does.

[Jagjit]

Hi Datamiser

O.K this is hard coded 12 , what should be the sql like statement if value is to get from the variable.

Thanks

[IanS]

Are you using DAO or ADO ?

In VB6 DAO and JET databases I always used the * as a wild card.

SELECT * FROM m_item WHERE icode like '12*'

should work just fine and will find strings that start with '12'

If you want strings where '12' appears anywhere in the string then '*12*' should do it.

I mentioned VB6 DAO and JET. if you were doing this with ADO or in .Net and oledb then you would need to use the '%' percent symbol. But for DAO you can use the * symbol.

[Jagjit]

Hi Ians

12 is hardcoded , what should i need to do if i have to get value from a variable.

Thanks

[IanS]

Do you mean 12 is a variable?

Dim someValue as String

someValue = "12" '// Or any other string - perhaps from an input box or something

esql = "SELECT * FROM m_item WHERE icode LIKE '" & someValue & "*' "

It may be difficult to see the ' and " symbols above but after LIKE theres a ' and a " very close together.

[Jagjit]

Dear Ians


I have written this but is is not searching whereas record exits

strsearch = InputBox("Search For", "Search Input Box")
strsql = "select * from m_itemmaster where icode LIKE '" & strsearch & "%' " . I am using ADO

Thanks

[IanS]

It's been many years since I did anything with VB6 and ADO. I always found DAO to be faster when accessing a JET/Access database. I only used ADO when accessing a SQL Server database.

So I can't remember if VB6/ADO/Jet database used % or * as the wildcard.

I assume you've tried using * symbol instead ?

You're sure the data exists in your database as a string with no leading spaces ? - also make sure the data from the Inputbox does not have any leading/trailing spaces

Can you show the whole code section ?

[IanS]

It's been many years since I did anything with VB6 and ADO. I always found DAO to be faster when accessing a JET/Access database. I only used ADO when accessing a SQL Server database.

So I can't remember if VB6/ADO/Jet database used % or * as the wildcard.

I assume you've tried using * symbol instead ?

You're sure the data exists in your database as a string with no leading spaces ? - also make sure the data from the Inputbox does not have any leading/trailing spaces

Can you show the whole code section ? I mean you show us how you build the esql string but you don't show how you're using it or retrieving any values from the database so posting the whole section from collecting the user input all the way to evaluating the DB return values.

[Jagjit]

Hi Ians

Problem is when i search with variable , if i harcode it it works fine.

Dim rstitem as adodb.recordset

strsql = "select * from m_itemmaster order by icode"
Openrstitem (strsql)

Private Sub Search()
Dim strsearch, strsql As String
strsearch = InputBox("Search For", "Search Input Box")
strsql = "select * from m_itemmaster where icode LIKE '" & Trim(strsearch) & "%' "
Openrstitem (strsql)
if rstitem.recordcount > 0 then
call display
endif
End Sub



icode
A00LB0C25343
A00LO2C27338
A00SW0C19975
A01SC2C26652
A09BT0C26400
A09GT0C26400
A09ST0C25589
A12ST0C25236
E12BT0C23896
E12ST0C23896

[IanS]

If you were searching for '12' and wanted to get results where '12' appears ANYWHERE in the string such as A12ST0C25236 and E12BT0C23896 then you need to put wildcard at both sides of the variable

strsql = "select * from m_itemmaster where icode LIKE '%" & strsearch & "%' "

[brss]

More like:

strsql = "SELECT * FROM [m_itemmaster] WHERE [icode] LIKE '%" & Trim(strsearch) & "%'"

[Jagjit]

Hi brss

Thanks a lot , it is working .

Thanks