How to use wildcard character in query?

I'm trying to use a textbox to search and display records as each letter is
typed in - similar to the behavior of a combo box. But for some reason I
can't seem to get the wildcard search character to work in my query. In the
example below, I have several records in tblEntity that begin with "m" and
"mi" and one record with the company name "microsoft". I get a match only
when I type in the full string "microsoft".

Why no match before then?

If you have any suggestions on a better way to do this, I'm all ears, but
I'm still curious why the wildcard is not working.

Thanks in advance.

[qryFind]
SELECT Entity_ID
FROM tblEntity
WHERE Company Like QryPrm("frm0","txtFind");
[note: QryPrm is a function that returns the parameter]

[procedure on frm0]
Private Sub txtFind_KeyUp(KeyCode As Integer, Shift As Integer)
strFind = Me!txtFind & "*"
lngEid = Nz(DMax("Entity_ID", "qryFind"), 0)
Debug.Print strFind
Debug.Print lngEid
End Sub

[output]
m*
0
mi*
0
mic*
0
micr*
0
micro*
0
micros*
0
microso*
0
microsof*
0
microsoft*
1466

re: How to use wildcard character in query?

On Sat, 01 Jan 2005 04:55:55 GMT, "deko"
<www.clearpointsystems.com@use_contact_form.com> wrote:
[color=blue]
>I'm trying to use a textbox to search and display records as each letter is
>typed in - similar to the behavior of a combo box. But for some reason I
>can't seem to get the wildcard search character to work in my query. In the
>example below, I have several records in tblEntity that begin with "m" and
>"mi" and one record with the company name "microsoft". I get a match only
>when I type in the full string "microsoft".
>
>Why no match before then?
>
>If you have any suggestions on a better way to do this, I'm all ears, but
>I'm still curious why the wildcard is not working.
>
>Thanks in advance.
>
>[qryFind]
>SELECT Entity_ID
>FROM tblEntity
>WHERE Company Like QryPrm("frm0","txtFind");
>[note: QryPrm is a function that returns the parameter]
>
>[procedure on frm0]
>Private Sub txtFind_KeyUp(KeyCode As Integer, Shift As Integer)
> strFind = Me!txtFind & "*"
> lngEid = Nz(DMax("Entity_ID", "qryFind"), 0)
> Debug.Print strFind
> Debug.Print lngEid
>End Sub
>
>[output]
>m*
> 0
>mi*
> 0
>mic*
> 0
>micr*
> 0
>micro*
> 0
>micros*
> 0
>microso*
> 0
>microsof*
> 0
>microsoft*
> 1466
>
>[/color]
Hi
This won't work for each character typed in, the Dmax function will
only see what you have typed when the record is saved.
Why not use a combo box?
David

re: How to use wildcard character in query?

> This won't work for each character typed in, the Dmax function will[color=blue]
> only see what you have typed when the record is saved.[/color]

10-4. what I did was bound the textbox and and then did everything on
keyup - i.e. saved the record, reset the rowsource of my list to the query
below, and then set selstart of the textbox to the length of the string it
contains. works fantastic. The key to getting the wildcard search working
was the magic '*' + string + '*' syntax

SELECT DISTINCT Entity_ID, FullName, Company
FROM qry1000
WHERE Company Like (QryPrm("frm0","QuickSearch") + '*') Or LastName Like
(QryPrm("frm0","QuickSearch") + '*') Or FirstName Like
(QryPrm("frm0","QuickSearch") + '*');
[color=blue]
> Why not use a combo box?[/color]

lots of reasons...