Why Doesn't My SQL Work

[egun]

This VBA snippet works and returns a single record (because there is only one
"1150" in the table):

sqltext = "SELECT tlkpWBS.ID FROM tlkpWBS WHERE tlkpWBS.WBS Like ""1150""; "
Set con2 = CurrentProject.Connection
Set rs = New ADODB.Recordset
rs.Open sqltext, con2, adOpenStatic, adLockReadOnly, adCmdText

If I change the 1150 to 115* as below, I get zero records returned. Can
someone tell me why? It works okay in a query with exactly the same SQL
string.

sqltext = "SELECT tlkpWBS.ID FROM tlkpWBS WHERE tlkpWBS.WBS Like ""115*""; "

Thanks,

Eric

 

[Chris O'C via AccessMonster.com]

ADO requires % as the wild card, not *. It's the ansi standard.

sqltext = "SELECT tlkpWBS.ID FROM tlkpWBS WHERE tlkpWBS.WBS Like '115%';"

Chris

 

[Clifford Bass]

Hi Eric,

The use of the asterisk is non-standard SQL. So when using ADO you
have to use the standard SQL wild cards. Percent (%) instead of asterisk and
underscore (_) instead of the question mark (?). Also, you do not need the
semicolon.

sqltext = "SELECT tlkpWBS.ID FROM tlkpWBS WHERE tlkpWBS.WBS Like ""115%"""

Clifford Bass

 

[egun]

Thank you both for your responses! I'm great at VBA in general, terrible at
databases and SQL.

Eric

 

[Clifford Bass]

Hi Eric,

You are welcome!

Clifford Bass