Share via

Access 2010 Like Function Not Importing in Excel 2010

Anonymous
2011-12-22T00:39:00+00:00

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,

Microsoft 365 and Office | Access | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

  1. Anonymous
    2012-01-03T22:31:35+00:00

    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

    10+ people found this answer helpful.
    0 comments

Answer accepted by question author

  1. Anonymous
    2011-12-22T15:08:26+00:00

    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

    5 people found this answer helpful.
    0 comments

9 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2014-09-05T11:10:51+00:00

    Unfortunately, Nick, the “%” and / or “*” can both be used as wild card functionality.  Although they may behave differently based upon the version of ANSI you are using.  I don’t believe ANSI was created by Microsoft and I think the standard was made by the American National Standards Institute.  Applications can then decide which standard they want to use.  Some applications make use of ANSI 89 and some ANSI 92.  From what I can see Excel by default makes use of the ANSI 92 standard, similar to Microsoft SQL Server.  Access databases by default makes use of a different version of ANSI, hence the difference in wild card behavior.  With that being said, Access does give you the option to make use of ANSI 92 in case you need to integrate with other applications that also make use of it.

    In Access 2010, this option can be changed by doing the following:

    File Menu -> Access Options -> Object Designers -> Check options below SQL Server Compatible Syntax (ANSI 92).

    In Access 2007, this option can be changed by doing the following:

    Office Button -> Access Options -> Object Designers -> Check options below SQL Server Compatible Syntax (ANSI 92).

    In Access 2003, this option can be changed by doing the following:

    Tools menu -> Option -> Tables / Queries -> Check options below SQL Server Compatible Syntax (ANSI 92).

    The following articles discuss these options within Access:

    About ANSI SQL Query mode (MDB) - http://office.microsoft.com/en-us/access-help/about-ansi-sql-query-mode-mdb-HP003070483.aspx

    Access Wildcard Character Reference - http://office.microsoft.com/en-us/access-help/access-wildcard-character-reference-HA010076601.aspx

    Comparison of Microsoft Jet SQL and ANSI SQL - http://office.microsoft.com/en-us/access-help/comparison-of-microsoft-jet-sql-and-ansi-sql-HP001032250.aspx

    Best Regards,

    Nathan O.

    Microsoft Online Community Support

    Why did Microsoft decide to use one ANSI standard in Excel and an alternative one in Access?

    I basically would need to rewrite all my hundreds of querys to get them to work if I switch ANSI in Access.

    Can you switch ANSI standards in Excel?

    yours sincerely,

    Jesse Cooper

    1 person found this answer helpful.
    0 comments
  2. Anonymous
    2011-12-27T21:25:29+00:00

    Unfortunately, Nick, the “%” and / or “*” can both be used as wild card functionality.  Although they may behave differently based upon the version of ANSI you are using.  I don’t believe ANSI was created by Microsoft and I think the standard was made by the American National Standards Institute.  Applications can then decide which standard they want to use.  Some applications make use of ANSI 89 and some ANSI 92.  From what I can see Excel by default makes use of the ANSI 92 standard, similar to Microsoft SQL Server.  Access databases by default makes use of a different version of ANSI, hence the difference in wild card behavior.  With that being said, Access does give you the option to make use of ANSI 92 in case you need to integrate with other applications that also make use of it.

    In Access 2010, this option can be changed by doing the following:

    File Menu -> Access Options -> Object Designers -> Check options below SQL Server Compatible Syntax (ANSI 92).

    In Access 2007, this option can be changed by doing the following:

    Office Button -> Access Options -> Object Designers -> Check options below SQL Server Compatible Syntax (ANSI 92).

    In Access 2003, this option can be changed by doing the following:

    Tools menu -> Option -> Tables / Queries -> Check options below SQL Server Compatible Syntax (ANSI 92).

    The following articles discuss these options within Access:

    About ANSI SQL Query mode (MDB) - http://office.microsoft.com/en-us/access-help/about-ansi-sql-query-mode-mdb-HP003070483.aspx

    Access Wildcard Character Reference - http://office.microsoft.com/en-us/access-help/access-wildcard-character-reference-HA010076601.aspx

    Comparison of Microsoft Jet SQL and ANSI SQL - http://office.microsoft.com/en-us/access-help/comparison-of-microsoft-jet-sql-and-ansi-sql-HP001032250.aspx

    Best Regards,

    Nathan O.

    Microsoft Online Community Support

    1 person found this answer helpful.
    0 comments
  3. Anonymous
    2011-12-27T17:37:49+00:00

    I appreciate the quick reply, but this is not the answer.  This "&" and "*" confusion shows up often in web searches and is just another example of the insane inconsistencies of Microsoft.  The Like function between Access queries and Excel pivots worked for years in XP and prior versions, but somehow MS screwed it up when it created 2010 (or maybe starting in 2007).

    I think the answer for anyone considering migrating to 2010 is...don't.  Unless you think rotating your keyboard 180 degrees and then trying to type is easier, 2010 is not for you.  You might as well start fresh with some other database.

    0 comments