Access 2010 Like Function Not Importing in Excel 2010

In Access I have a table field with a 4-digit policy number. I created a query to provide all policies beginning with an A.  That is, Criteria is Like "A*" 

In Access, I get all the A results.

Next, I open Excel 2010 and use "Insert Pivot Table" but no A policy numbers are imported.  (If I delete the Like "A*", the refresh gives me all policy numbers, so the import works. 

It appears that 2010 has an issue with Excel not recognizing the wildcard from Access.  Any ideas?  Thanks,

 

Question Info


Last updated October 18, 2019 Views 4,994 Applies to:
Answer
Answer

Hi Nick,

 

When you link to a SQL Server table within Access, you will make use of the Access Database Engine when querying that linked table.  Because of this, you will make use of the same wild card characters that Access normally makes use of.  For example, like “A*” would return all the data that begins with the letter A.  This will also work if you run a query against a local Access table.  However, Excel, makes use of a different wild card character because it uses the ANSI 92 standard.  Thus, if you want to use wild cards in query that is run within Excel, you will have to make use of the % wild card sign.  This % wild card sign will only bring back the expected results when the query is run from Excel or if you change the setting I mentioned.  If you don’t change this setting and run the query from Access, you will get unexpected results.  As you have seen, when you change this setting within Access the criteria changes to Alike “A*”, which doesn’t produce the same results as it did before.  If you want this query to run within Access and show all records that start with the letter A, the syntax would be ALIKE “A%”.  The reason you have to change the * to a % is because you told Access to make use of a different ANSI standard, which in turn tells it to use a different wild card character. 

 

So in summary:

+>If you want to make use of an Access query within Excel or make use of wild card characters within Excel, you will need to make use of the % wild card.

+>If you want to run this same query within Access, you will need to use the * wild card to get the same results.

+>If you want the query to run within Excel and in Access, you will need to make use of the % wild card sign and also change the setting I mentioned.  After changing this setting, you will need to ensure you change the wild cards in your old queries to %.

+>The other option would be to have two queries and not change the setting at all.  You could then make use of a * wild card in Access.  The second query would make use of the % wild card.  The second query would not return the expected results from within Access, but when you run it from Excel it should work.

 

 

As far as Excel goes, I believe you can find MS Query by opening your Excel file and then click on the data tab and then within the “Get External Data” section click on From Other Sources -> From MS Query.

 

Best Regards,
Nathan O.
Microsoft Online Community Support

6 people were helped by this reply

·

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

Answer
Answer

Hi Nick,

 

I believe Excel uses ANSI 92 syntax for its SQL syntax , so I would recommend changing the “*” to “%” and see if that works.

 

Best Regards,

Nathan O.

Microsoft Online Community Support

3 people were helped by this reply

·

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.