SQL Statement Problem

[pewilson]

I have a SQL string that is giving me a problem and I'm not quite sure what is wrong. It's not returning any values, so I'm not sure if my syntax is incorrect. This is the SQL statement in question. Any and all help is greatly appreciated.

Thank you

With rst.Open "Select ExhibitorID,ExhibitorName From Exhibitor Where ExhibitorName LIKE " & "'" & Node.text & "*'", cnn, adOpenKeyset
End With Paul
paul_wilson74@hotmail.com

 

[PaulBricker]

With rst.Open "Select ExhibitorID,ExhibitorName From Exhibitor Where ExhibitorName LIKE """ & Node.text & "*"", cnn, adOpenKeyset

Try this. I just changed a couple "'s.

Paul

 

[cmmrfrds]

The problem is the * change to %

With rst.Open "Select ExhibitorID,ExhibitorName From Exhibitor Where ExhibitorName LIKE " & "'" & Node.text & "*'", cnn, adOpenKeyset
End With

rst.Open is an ADO object and Microsoft changed ADO to follow ANSI SQL standards. The * is a wildcard in Jet but the % is the wildcard in other sql databases. The confusion comes in because if you are in the query builder in Access 2000 + the objects are DAO and Jet therefore the * would be the wildcard. If you build recordsets in vba code using ADO then the % is the wildcard.

 

[pewilson]

Thanks Everyone! By changing the * to % work just fine. Thanks again Paul
paul_wilson74@hotmail.com