database wild card

[ken@ngc]

I am having trouble using wildcard matching in database toolkit queries.  When working directly in Microsoft Access a query using the expression Like "File*" returns the expected data but  when sent through the tool kit results in an error complaining about illegals characters.

Error -2147217904 occurred at Conn Execute.vi->DB Tools Select Data.vi->SQL_like.vi

Possible reason(s):
.  Invalid string: specified field does not exist or contains an unsupported character

If I replace "File*" with 'File*' then no no error occurs but no results are returned because, presumably, the * is no longer treated as a wildcard.

[jcarmody]

Try using a % for the wildcard.

[ken@ngc]

% inside single quotes works.  Any other wildcard characters work inside single quotes?  Is this documented anyplace?  Thanks.

[mikeporter]

Actually, the % is the SQL standard wildcard for use in LIKE clauses. I'm surprised that something else worked at all...

Mike...

[DiscoBall]

Hi Ken, does using two ticks('') instead of the double quotes (") work in getting the wildcard to go through?

[ken@ngc]

Using double ticks producing a slightly different error message:

Error -2147217900 occurred at Conn Execute.vi->DB Tools Select Data.vi->SQL_like.vi

Possible reason(s):
Exception occured in Microsoft OLE DB Provider for ODBC Drivers: [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'item Like ''C*'''. in Conn Execute.vi->DB Tools Select Data.vi->SQL_like.vi

The outermost ticks were added by the error processing.  The double inner ticks ''C*'' are mine.