[RESOLVED] Search and Sort Question

[Smeg-Head]

VB6, Excel database, and I am not that good at these strings

I have a sql string which pulls all data from an employee directory db on startup. I get every listing in the db for each employee.

In a textbox_change routine for the first name, I have a sql string as follows:

    strSQL = "SELECT FirstName" _
           & "     , LastName" _
           & "     , Dept" _
           & "     , Sub" _
           & "     , Loc" _
           & "     , Ext" _
           & "     , EmpID" _
           & "  FROM [mytable$] " _
           & "  WHERE FirstName='" & txt_First.Text & "'" _
           & " ORDER BY LastName" _
           & "        , FirstName"

Problem is when I start typing in the textbox, it does exactly what it says it does and not what I want it to do.

What I want is like an auto complete. When I type the entire listings that get shown in a listview need to decrease until I have finished typing. I need something like a wild card in there.

& " WHERE FirstName='" & txt_First.Text & "*'" _

what do I need here?

[si_the_geek]

To use a wildcard you need to use Like instead of = , eg:

& " WHERE FirstName Like '" & txt_First.Text & "*'" _

Note however that * (and ? for a single character) might not be the wildcard - as the standard is actually % (and _ ).

[Smeg-Head]

OK.. I got it. Kinda threw me off but this works like I was wanting.

Thanks for your help!

 & "  WHERE FirstName Like'" & txt_First.Text & "%'" _

[Smeg-Head]

Even though it is resolved, one last question. (Same topic)

How do you add more than one where statement.

Say I want something like this:

    strSQL = "SELECT FirstName" _
           & "     , LastName" _
           & "     , Dept" _
           & "     , Sub" _
           & "     , Loc" _
           & "     , Ext" _
           & "     , EmpID" _
           & "  FROM [mytable$] " _
           & "  WHERE FirstName Like'" & txt_First.Text & "%'" _
           & "     , LastName Like'" & txt_Last.Text & "%'" _
           & " ORDER BY LastName" _
           & "        , FirstName"

[si_the_geek]

You do the same as you would for an If statement in VB - use AND or OR (or a combination of both), as appropriate.

So, if you want both conditions to be true, use And:

           & "     AND LastName Like'" & txt_Last.Text & "%'" _

..and if you want either of them (or both) to be true, use OR.

[Smeg-Head]

I figured as much and had tried that.

I see now I had a typo.

Thanks again!