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

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

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
..

wrote in message
oups.com...
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

On Sep 19, 1:45 pm, "John Spencer" wrote:
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.

--

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.



On Sep 19, 7:45 am, "John Spencer" wrote:
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
.

wrote in message

oups.com...

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

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
..

"Jamie Collins" wrote in message
ups.com...
On Sep 19, 1:45 pm, "John Spencer" wrote:
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.

--

On Sep 19, 4:34 pm, "John Spencer" wrote:
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.

--