Why Doesn't My SQL Work

  • Thread starter Thread starter egun
  • Start date Start date
E

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
 
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
 
Thank you both for your responses! I'm great at VBA in general, terrible at
databases and SQL.

Eric
 
Back
Top