Searching for a string in an access database

[robbie_j0]

Hi there,

I have a system built in VB_6 professional which reads from an access database.
I need to search for a specific record using a word or short description entered by the user. This will link to the DESCRIPTION field in the database itself. Not being case sensitive would be a bonus.

Does anybody have any sample code for such a function? or does anybody know where i could find such code?

Many thanks

[calcop]

do it though your SQL query. I havent used an Access SQL query in years, but it might be similar to:

Dim searchText as String
Dim sqlQuery as String
searchText = searchDescription.Text
sqlQuery = "SELECT * FROM `whatever_table` WHERE `description` LIKE `%" & searchText & "%' LIMIT 10;"

Then execute the query with searchText.

[AstroNox]

I believe Access does not have the LIMIT keyword, and that is only an extension specific to MySQL. MS SQL Server uses TOP but I don't think Access has this function. Also, Access and MS SQL Server uses square brackets ( [] ) to delimit its variable names, while MySQL uses the backtick ( ` ). Other than these issues, the LIKE operator would settle your string searching needs.

For case-insensitivity, first of all, I do not know if this function is available in VB because all I've used is VB.NET. Note the underlined portions:

Dim searchText as String
Dim sqlQuery as String
searchText = searchDescription.Text
sqlQuery = "SELECT * FROM [whatever_table] WHERE TOLOWER([description]) LIKE `%" & searchText.ToLower() & "%';"

When you set both the field you are matching with and the match text to lowercase (or uppercase), it is equivalent to a case-insensitive match, albeit the possible inefficiency of matching long strings of opposite-cased text.

You might want to take a look at this two posts for some issues related to this: http://www.daniweb.com/techtalkforums/post200141-5.html
http://www.daniweb.com/techtalkforums/post200190-9.html

Hope this helps

[Comatose]

Ok, this is a vb 4, 5, 6 forum, not a vb.net forum. Should you be looking for that forum, you can find it here: http://www.daniweb.com/techtalkforums/forum58.html . var.tolower() is a .net method, and is not available in vb6. Let's try to keep related information grouped in their respective forums.

[AstroNox]

I'm sorry but I didn't know that ToLower() is a VB.NET specific function. You see, I've never programmed in VB4/5/6 before, but I had done VB.NET and VBA so I thought I might be able to share something useful. Just trying to help but I'll keep that in mind next time. Thanks!

[Comatose]

We appreciate all the help we can get, certainly, I just want to avoid having people trying to build something in one language get further complicated by examples for another.