Using LIKE operator in VBA

[JMB]

I'm running into problems with an append query in Access
2000 VBA. If I use the LIKE operator the query returns no
results if executed through VBA, however the identical
query runs just fine in QBE. The code looks like this:

strSQL = "INSERT INTO table ( LastName ) SELECT LastName
FROM SomeTable WHERE (((LastName) LIKE '" & somevalue
& "*'));"
Application.CurrentProject.Connection.Execute strSQL

The code returns no value. However if I print strSQL to
the immediate window and then paste it in using the create
query in SQL view and run it it works.

The table I'm appending to has no required fields and all
text fields are set to allow zero length fields and there
are no keys set.

What am I missing here?

 

[John Viescas]

You're using ADO Execute, so you must use the ANSI wildcard:

strSQL = "INSERT INTO table ( LastName ) SELECT LastName
FROM SomeTable WHERE (((LastName) LIKE '" & somevalue
& "%'));"

Your original code would work if you used a DAO Execute:

CurrentDb.Execute strSQL, dbFailOnError

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)