CODEWITHSUNDEEP
sqlexcelvba
Nick
Nick

Reputation: 3

Problem using wildcards(*) in Excel vba sql statement

Ive been trying to get this sql statement to work properly through excel vba.

SELECT * FROM Documents WHERE (((Documents.doc_Comment) Like *" & [SStr] & "*));

This works 100% in access, what i want it to do is allow for a search based on part of the doc_comment field.

example Search for BOB

The search would return only the first and second.

As i said above the access query is easy to get working but as soon as i add in excel it all goes to hell. Ive tried using stored queries in access to no avail, even tried writing a temptable but as soon as i call the query from excel and pass the parameter the query fails and gives no results.

Ive tried searching around and cant find anything that helps.

Thanks alot

Edit note-made example data more readable

Upvotes: 0

Views: 5675

Answers (2)

Jack Sadowski
Jack Sadowski

Reputation: 1

The only wildcard that worked for me was %. Edit - my experience applies to Excel with ADODB driver only. In my project I had the same problem - "*" symbol used as wildcard gave me errors. Once I used "%" it worked. I did not spend time investigating...

Upvotes: -1

Fionnuala
Fionnuala

Reputation: 91316

If you are using ADO in Excel, it is case sensitive and the wild card is % not *.

To be a little clearer, the wild card for ADO is always %.

Upvotes: 2

Related Questions