Excel linked to Access w/"Like" not working

[Joe M]

Not sure whether to post this in the Access group or here, but trying here
first. Running MS Office 2007. I'm getting bad results in Excel linking
back to an Access Query that used "like" in the criteria. Easier to explain
with an example. I use quotes for all values, but all values are without
quotes.

Start with an Excel file that has 1 column and 2 rows.
The value in cell A1 is "abc".
The value in cell A2 is "xyz".

I go to Access and link to the Excel file above.
I create a query with "like "a*"" in the criteria.
When I run the query, I get a single row of "abc" in results, as expected.

I go to Excel and create a new file, linking back to the query I just
created in Access. When I refresh the data, it does not return the "abc".
It doesn't return anything.

I know I have all my external links set up correctly because if I remove the
"like" criteria in Access, when I go back to Excel and link to the Access
Query, it returns both rows as you would expect.

Why is Excel not liking the Access "Like" criteria selection? How do I fix
or get around this? Thanks,

Joe (joemagiera at ameritech dot net)
(e-mail address removed)

 

[Joe M]

Hi,

I tried using % instead of *. Interesting results. The example then worked
when I got back into Excel, but if I ran that in Access, it didn't get any
results there!

So I can use 'like "a*"' to get the query to work in Access, but not work
when imported back into Excel.

And I can use 'like "a%"' to get the query to work when imported back into
Excel, but doesn't produce any results in Access.

Any way to have it work for both?

I did put both into Access, i,e., 'like "a*"' or 'like "a%"' and then it did
produce results in both Access and when imported back into Excel, but that
seems like an awefully clunky way to do it.

Joe