Using 'Find' and wildcards

[99mel]

Is it possible to use '_' and % in the find statement?

I can do: B% and that will bring back all the values beginning with B.

I can't do %B, it brings back an error.

I can't do b_b either, it should bring back bob but doesn't find anything.

The recordset is coming from an SQL server.

Any info?

Thanks in advance

 

[RichardF]

try using * instead of %
and ? instead of _ ?

 

[99mel]

nope doesn't work either... i've used SQL Query Analyzer and enetered excatly the same LIKE after a select statment... it works fine.

I just get an error though when trying to do the following:
Searchstr = "%a"
myrs.Find TableName & " LIKE '" & Searchstr & "'"

I get the error:
Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.

Any suggestions?

 

[rtshort]

Don't know exactly what you are trying to query, so I'll do the best I can. Using the "like" word in SQL you to use both as you have suggested.

Searchstr = "'%a%'"
myrs.Find TableName & LIKE & Searchstr

That should find all tables with an "a" in the table name.

Searchstr = "'A%'"

Should find all tables that begin with "A"

Searchstr = "'%a"

Should find all tables that end in "a"

In SQL the ' is used as delimiters to confine the search word and the % is used only in the LIKE statement to let SQL know it is not the whole word. If you start with the beginning of the word (e.g. search for Betty with Bet) you would use (LIKE 'Bet%') because the first part of the word is complete. If you use (LIKE '%ett%') it will look for ett in the middle of the word and would find it that way. To use the end of the name you would use (LIKE '%tty')

Of course in VB you have to use the " where it is needed.
Hope this helps and is not confusing


Every day above ground is a GOOD DAY!!!

 

[99mel]

I enter '%a' into a text box called text1 then do the following string:

myrs.Find FieldName & " LIKE '" & text1.text & "'"

This brings back the error:
Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.

Shouldn't this search work?? it works with the '%' after the string?