[RESOLVED] Search and Sort Question

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:

Code:

---

    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?

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

Code:

---

& " 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 _ ).

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

Thanks for your help!

Code:

---

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

---

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:

Code:

---

    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"

---

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:

Code:

---

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

---

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

I figured as much and had tried that.

I see now I had a typo.

Thanks again!