VBScript - SQL "LIKE" not working as expected

Hi Everyone,

I have a simple VBScript to read the contents of an Access 2003 database.

The database contains only 6 rows (4 columns):

ID,LastName,FirstName,Age
1,Petra,Andrea,15
2,Petrosky,Mathew,35
3,Smith,John,30
4,Smith,Alfred,20
5,Smithy,Sandra,22
6,Zorba,Andy,20

The VBScript shown below works but only if I remove the "*" from the value of the LIKE clause. For example, LIKE 'Smith' returns records 3 and 4, but if I code LIKE 'Smith*' nothing is returned.

I would expect it to returns records 3, 4 and 5.

When I run the same SQL inside Access, it works.

The column LastName is indexed in the table.

Any help greatly appreciated.
John.

------------------------------

Dim rs
Dim conn
Dim strConnect

Set conn = CreateObject("ADODB.Connection")
strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=.\Database1.mdb"
conn.Open strConnect
strSQLQuery = "SELECT Table1.ID, Table1.LastName, Table1.FirstName, Table1.Age FROM Table1 WHERE (((Table1.LastName) LIKE'" & "Smith*'))"
Set rs = CreateObject("ADODB.Recordset")
rs.Open strSQLQuery, conn, 3, 3
cntr = 0
While Not rs.eof
cntr = cntr + 1
WScript.Echo(rs(0) & "," & rs(1) & "," & rs(2) & "," & rs(3))
rs.MoveNext
Wend
conn.close
Set conn = Nothing
WScript.Echo("Records returned: " & cntr)
WScript.Quit(0)

You should use a % instead of a *. Also you have a syntax problem in the code you posted above. The end of your query should look like this:

LIKE 'Smith%'

Thank you so much!

You rock.

Awesome! Glad I could help out.