Search Multiple Columns in a Datagrid/Recordset

[dedo8816]

Hi, been a while since i was on this.

Im creating a label making program, one which uses information on an Access Database to compile its information.
Basically you type a part number and it automatically fills in the information in the appropriate fields on another form which then prints to the label.

In the Database there are 4 fields i need to search or .find
[No]
[Description]
[Description2]
[Description3]

I have created a simple Dataenvironment to connect to my database and have use a .find meathod of searching, however the .find meathod only searchs one column in the database and i need it to search all 4. Ive also tried to use a .filter meathod but im afraid i can't figure out how to stucture this meathod as i would like to have the capabilities of only typing in a few letters/numbers and having it perform the search anyway.

Can anyone help?

Thanks in advance...

 

[dedo8816]

Sorry meant to say im Using Visual Basic 6...

 

[Andrzejek]

I use DataEnvironment every day for building SQLs, but I would use ADO to do what you want.

You can create a recordset this way:

str = "SELECT * From MyTableInAccess WHERE " _
& "           [No] LIKE '%" & txtSearch.Text & "%' OR  " _
& "  [Description] LIKE '%" & txtSearch.Text & "%' OR " _
& " [Description2] LIKE '%" & txtSearch.Text & "%' OR " _
& " [Description3] LIKE '%" & txtSearch.Text & "%' "

rst.Open str, Cn

Have fun.

---- Andy

 

[BobRodes]

<one which uses information on an Access Database
I'm sure Andy will be the first to tell you that you need to substitute "*" for "%" in his string for it to work in Access. Well, the second to tell you now that I have.

Now, I have a piece of code somewhere that uses Find to search multiple columns, taking advantage of a known sort order. But, I'd use Filter instead; Filter will work on multiple columns. Just set the recordset's Filter property to a valid SQL WHERE clause.

You have to decide whether to Filter your existing recordset or go get a new recordset with a new query. There are tradeoffs to both methods; I generally work with both filters and re-queries.

HTH

Bob

 

[dedo8816]

Andy thanks, im a complete novice when it comes to SQL, i need to read up on it.
I would use an ADO but i want the datagrid to display all my information.

Bob like i said in my original post i can never figure out how to write a filter. everytime i do i get error messages.

Perhaps you know what im doing wrong?

DataEnvironment1.rsCommand1.Filter ("'" & (TxtSearch.Text) & "'")

Thanks again

 

[JoeAtWork]

For the Filter property, the syntax is the same as a WHERE clause but without the WHERE.

So:

DataEnvironment1.rsCommand1.Filter ("Description='" & (TxtSearch.Text) & "'")

And Andy's use of % in ADO is correct, even though it's connecting to an Access database.

 

[dedo8816]

DataEnvironment1.rsCommand1.Filter ("Description='" & (TxtSearch.Text) & "'")

Got an error: Invalid use of Property.

Points to .Filter in the code...

Ill give the ADO a go, never use them to be honest. Might save some time...

Thanks

 

[JoeAtWork]

Oooops, Filter is a property, not a method, therefore:

DataEnvironment1.rsCommand1.Filter [COLOR=red]=[/color] "Description='" & (TxtSearch.Text) & "'"

 

[BobRodes]

<you know what im doing wrong?

Just as Joe mentions, you're using it as if it were a method. A filter evaluates to a valid WHERE clause in SQL. So, for example, the SQL statement

Select * from customers where custid = 'ALFKI'

would be rendered thus (where rs is a valid ADO Recordset object):

rs.Open "Select * from customers"
rs.filter = "custid = 'ALFKI'"

HTH

Bob

 

[dedo8816]

Hi, the below code worked for me, however is works much like the .Find meathod.

DataEnvironment1.rsCommand1.Filter = "Description='" & (TxtSearch.Text) & "'"

How do i code this to work with a partial piece of the search criteria?

i.e. to find MS24694-S29 i could type "MS24694-*" and have everything that has this number in the description will fill the datagrid?

 

[BobRodes]

<is works much like the .Find meathod.

It's a bit different. First, the find method simply goes to a record in the recordset. The filter property restricts the cursor to only those records matching the filter, so it's sort of like going and getting a sub-recordset. However, you don't go get anything. Second, the Filter property allows you to put logical ANDs in it. So, you could say something like "Description like 'a*' and ID like 'x*'" in a filter.


<How do i code this to work with a partial piece of the search criteria?

You use the LIKE operator (instead of the = sign) with a wildcard:

DataEnvironment1.rsCommand1.Filter = "ID like 'MS24694-*'"

HTH

Bob

 

[dedo8816]

Bob, thanks for your help but im afraid i really need to read up on SQL.
I am a bit of a novice as you can probably tell and have no idea about LIKE operators as such. I have functionality with my program at the moment, perhaps i can get it the way i want at a later stage but for now learning how to do it properly would probably be the better advantage.

Thank you once again for all you help...

 

[BobRodes]

<learning how to do it properly would probably be the better advantage.

I just TOLD you how to do it properly! See?

You use the LIKE operator (instead of the = sign) with a wildcard:

But let me elaborate a bit. The LIKE operator simply means that you are going to use a wildcard in the string expression. When you use the = sign, it means that the string must exactly match, so if you put = 'A*', you'll only get fields that actually have an A* in them. If you use LIKE 'A*', you'll get all the fields that begin with an A.

So, if I might make a suggestion: start "reading up on SQL" NOW. Read my posts, take what you have learned, and apply it in your code.

HTH

Bob

 

[dedo8816]

Thanks Bob, you were right, i completely missed the point altogether there. Your code worked a charm!

Thanks

 

[BobRodes]

You're most welcome.