like operator in recordset

[Sean]

Hello all,

I'm having a hell of a time trying to get the like
operator to work when creating a recordset.

I have a table noi-name-phone that has a record where the
field username is testuser. The following doesn't return
any records. If I change the testu* to testuser I get the
record. Can someone tell me what I am doing wrong?

Thanks.
Sean

Dim objconn As ADODB.Connection
Dim ssql As String
Dim qte As String
Dim recr As New ADODB.Recordset

Set objconn = CurrentProject.Connection
qte = "'"
ssql = "select username from [noi-name-phone] where
username like " & qte & "testu*" & qte

recr.Open ssql, objconn, adOpenKeyset, adLockReadOnly

Debug.Print recr.RecordCount

 

[Tim Ferguson]

qte = "'"
ssql = "select username from [noi-name-phone] where
username like " & qte & "testu*" & qte

ADO uses proper ISO wildcards, not MS-DOS ones. Try

WHERE UserName LIKE 'testu%'

More details in Help.


Tim F

 

[Guest]

ADO uses proper ISO wildcards, not MS-DOS ones. Try

WHERE UserName LIKE 'testu%'

More details in Help.


Tim F

.

Tim,

Thanks. That works fine. Can you give me pointers to the
help file? I've tried searching for ado help in my install
of access, but it never brings up any information.

Sean

 

[Tim Ferguson]

Thanks. That works fine. Can you give me pointers to the
help file? I've tried searching for ado help in my install
of access, but it never brings up any information.

On my system[1], it's acadp9.chm -- try searching your hard disk for it. In
the contents page[2] there is an entire section on Transact-SQL, which is
the flavour that adp files use.

[1] This is Access 2000 and MSDE 1.1; the file might be something different
with later versions. Try searching for acadp*.chm or at worst *.chm and
look for something suggestive.

[2] I now keep shortcuts to most of the help files on my desktop because
the context-sensitive features within Office progs are all fatally infected
with Microsofts two-fingered attitude toward basic user support. Going in
through the contents pages is often the fastest (and sometimes the only)
way of finding what you want.

Best wishes


Tim F