"like" works as query, but not in code (Access 2003)

[javaguy]

In my Access application I'm fetching data thru SQL statements, and
not directly thru a RecordSet object. In my statements, such as
select count(*) as reccount from [Survey Info] where [Last Name] =
"Smith"
things work OK. The returned count is 1.

But if my statement is
select count(*) as reccount from [Survey Info] where [Last Name]
like "Smit*"
then I get back a reccount value, but the count is 0.

If I copy this SQL and use it in a query builder, the SQL performs
properly. It doesn't perform OK in my code.

Here is the context:
....
Dim conn As ADODB.Connection
Set conn = CurrentProject.Connection
Dim sql As String
sql = "select count(*) as reccount from [Survey Info] where [Last
Name] like "Smit*"
Dim recSet As New ADODB.Recordset
recSet.Open sql, conn, adOpenStatic

If recSet.RecordCount <> 0 Then
Dim filterCount As Long
filterCount = recSet.Fields("reccount").value
End If
....
I've searched groups and other web pages, and the help files, but
don't see any mention of a performance difference or error in Access.
It works the same (badly) on Windows XP and Vista. Again, I'm using
Access 2003 with a MDB file.

Thanks in advance,
Jerome

 

[John Spencer]

Try using "%" as the wildcard instead of "*".

SELECT Count(*) as reccount from [Survey Info] where [Last Name] like
"Smit%"

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Jamie Collins]

Try using "%" as the wildcard instead of "*".

SELECT Count(*) as reccount from [Survey Info] where [Last Name] like
"Smit%"

And if they want something to work in all Query Modes, they could try
the ALIKE operator too:

SELECT Count(*) as reccount from [Survey Info] where [Last Name] ALIKE
'Smit%';

Jamie.

--

 

[javaguy]

Oh, yeah. I knew that Access had its own versions of these (_ and %,
vs. % and *). But since the * worked in the stand-alone query I
thought that it would also behave in the ADO Recordset.

Your answer has been a very big help to me. Thank you.

Try using "%" as the wildcard instead of "*".

SELECT Count(*) as reccount from [Survey Info] where [Last Name] like
"Smit%"

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

In my Access application I'm fetching data thru SQL statements, and
not directly thru a RecordSet object. In my statements, such as
select count(*) as reccount from [Survey Info] where [Last Name] =
"Smith"
things work OK. The returned count is 1.

But if my statement is
select count(*) as reccount from [Survey Info] where [Last Name]
like "Smit*"
then I get back a reccount value, but the count is 0.

If I copy this SQL and use it in a query builder, the SQL performs
properly. It doesn't perform OK in my code.

Here is the context:
...
Dim conn As ADODB.Connection
Set conn = CurrentProject.Connection
Dim sql As String
sql = "select count(*) as reccount from [Survey Info] where [Last
Name] like "Smit*"
Dim recSet As New ADODB.Recordset
recSet.Open sql, conn, adOpenStatic

If recSet.RecordCount <> 0 Then
Dim filterCount As Long
filterCount = recSet.Fields("reccount").value
End If
...
I've searched groups and other web pages, and the help files, but
don't see any mention of a performance difference or error in Access.
It works the same (badly) on Windows XP and Vista. Again, I'm using
Access 2003 with a MDB file.

Thanks in advance,
Jerome

 

[John Spencer]

Good idea if they are using Access 2000 or later. ALIKE (as far as I know)
does not work in earlier versions.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Try using "%" as the wildcard instead of "*".

SELECT Count(*) as reccount from [Survey Info] where [Last Name] like
"Smit%"

And if they want something to work in all Query Modes, they could try
the ALIKE operator too:

SELECT Count(*) as reccount from [Survey Info] where [Last Name] ALIKE
'Smit%';

Jamie.

 

[Jamie Collins]

ALIKE (as far as I know)
does not work in earlier versions.

Using a Jet 3.51 mdb, the ALIKE works fine for me in ADO using both
the Microsoft.Jet.OLEDB.3.51 and Microsoft.Jet.OLEDB.4.0 providers
respectively; also works fine for me in the Access97 interface (SQL
View of a Query object).

Jamie.

--