DaniWeb Forum Index > Software Development > Visual Basic 4 / 5 / 6

Visual Basic 4 / 5 / 6 RSS

Searching for a string in an access database

Please support our Visual Basic 4 / 5 / 6 advertiser: Programming Forums - DaniWeb Sister Site

•

Join Date: Oct 2005

Posts: 4

Reputation: robbie_j0 is an unknown quantity at this point

Solved Threads: 0

robbie_j0

Offline

Newbie Poster

Searching for a string in an access database

Nov 10th, 2005

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

•

Join Date: Oct 2005

Posts: 27

Reputation: calcop is an unknown quantity at this point

Solved Threads: 0

calcop

Offline

Light Poster

Re: Searching for a string in an access database

Mar 27th, 2006

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

 Help with Code Tags 
 Visual Basic 4 / 5 / 6 Syntax (Toggle Plain Text) 
Dim searchText as String
Dim sqlQuery as String
searchText = searchDescription.Text
sqlQuery = "SELECT * FROM `whatever_table` WHERE `description` LIKE `%" & searchText & "%' LIMIT 10;"
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.

•

Join Date: Mar 2006

Posts: 50

Reputation: AstroNox is an unknown quantity at this point

Solved Threads: 2

AstroNox

Offline

Junior Poster in Training

Re: Searching for a string in an access database

Mar 27th, 2006

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:

Help with Code Tags

(Toggle Plain Text)

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

Best Regards, God Bless,
AstroNox

•

Join Date: Dec 2004

Posts: 2,413

Reputation: Comatose is a jewel in the rough

Solved Threads: 211

Comatose

Offline

Taboo Programmer

Re: Searching for a string in an access database

Mar 27th, 2006

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.

•

Join Date: Mar 2006

Posts: 50

Reputation: AstroNox is an unknown quantity at this point

Solved Threads: 2

AstroNox

Offline

Junior Poster in Training

Re: Searching for a string in an access database

Mar 27th, 2006

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!

Best Regards, God Bless,
AstroNox

•

Join Date: Dec 2004

Posts: 2,413

Reputation: Comatose is a jewel in the rough

Solved Threads: 211

Comatose

Offline

Taboo Programmer

Re: Searching for a string in an access database

Mar 27th, 2006

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.