Excel 2003 not understanding Access 2003 "like"

[Guest]

I have a number of Access queries linked and displayed in an Excel workbook
by way of Data, Import External Data, Import Data. Some of the criteria and
select statements in the queries use the keyword "Like" (e.g. Like "*Smith"
to pull up all names ending in "Smith"). These statements seem to be
completely ignored by Excel so that records I should see are not appearing in
the workbook. This worked fine in versions 97 (from which we've just
upgraded) so is this a version 2003 bug? If so, is there a fix? (Apologies
for posting this twice but I wasn't sure if it's an Excel or an Access
problem.)

 

[Douglas J Steele]

How are you connecting to Excel: through DAO or ADO?

If you're using ADO, the wildcard character is %, not *.

 

[Guest]

Thanks but I'm not using code - just connecting to an Access query from Excel
using the import External Data command from the Data menu.

 

[Douglas J Steele]

Did you try using %, rather than *, just in case that's the problem?

 

[Guest]

Thanks Doug - that did the job. Didn't realise DAO (and now ADO) were used
for these links. Are there any utilities out there to help with this? My
queries are relatively simple but problems of course arise when the query
being linked to Excel is based upon other queries within Access - you can't
just replace * with % in earlier queries in the chain because Access itself
can't interpret the % sign.

 

[Douglas J Steele]

Unfortunately, I'm not aware of any