wildcard * don't work with sql query to mdb using ado

Hi, experts
 my code is
        cnDBF.Provider = "Microsoft.Jet.OLEDB.4.0"
       ' cnDBF.Properties("Extended Properties") = "dBase iv" commented now use .mdb
        cnDBF.Properties("Data Source") = Text1.Text
        cnDBF.CursorLocation = adUseClient
        cnDBF.Open
 
        sSQL = "SELECT * " & _
            "FROM kladr where mcode like '*000000';"
         Set rsRegion = New adodb.Recordset
        rsRegion.Open sSQL, cnDBF, adOpenDynamic, _
                       adLockBatchOptimistic, adCmdText

mcode is a string field and nothing records is selected, but the other wildcard
work true :
        sSQL = "SELECT * " & _
         "FROM kladr where code like '#####00000000';"
Can anybody help me/  

LVL 1
EugenyAsked:
Who is Participating?
 
Z03niECommented:
My friend, use percent symbol(%) instead of wildcard.
Something like :

        sSQL = "SELECT * " & _
            "FROM kladr where mcode like '%000000';"
0
 
anvCommented:
hi  Eugeny

actually  will work for SQL Server  Databases and % works for MS Access Databases...

so instead of * use %

as commented by Z03niE
0
 
jimbobmcgeeCommented:
anv, are you sure?

(taken from http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_la-lz_115x.asp)
%  -  Any string of zero or more characters. WHERE title LIKE '%computer%' finds all book titles with the word
        'computer' anywhere in the book title.

_   -  Any single character. WHERE au_fname LIKE '_ean' finds all four-letter first names that end with ean (Dean,
        Sean, and so on).

[ ]  - Any single character within the specified range ([a-f]) or set ([abcdef]). WHERE au_lname LIKE '[C-P]arsen'
        finds author last names ending with arsen and beginning with any single character between C and P, for
        example Carsen, Larsen, Karsen, and so on.

[^] - Any single character not within the specified range ([^a-f]) or set ([^abcdef]). WHERE au_lname
        LIKE 'de[^l]%' all author last names beginning with de and where the following letter is not l.

I was under the impression that, as Z03niE said, Eugeny should use '%'.

J
0
 
jimbobmcgeeCommented:
anv:

My apologies; you did actually say to use '%'.

J.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.