Office Settings & Configuration

How to Fix Microsoft Access Changing LIKE to ALIKE in Queries

Maira Mehtab Oct 14, 2026 868 views

Question details

The user needs to prevent Microsoft Access from automatically replacing the SQL LIKE operator with ALIKE, which breaks standard wildcard query criteria.

Product: Microsoft Access
Device & OS: not provided
Scenario: Writing or modifying SQL queries with wildcard characters in Microsoft Access.
Observed behavior: When creating or saving a query, Microsoft Access automatically changes the LIKE keyword to ALIKE, causing wildcard searches to fail or stop working as expected.

Before you start

Before changing database syntax settings, be aware that switching back to standard Access SQL will require you to use standard wildcards (like * and ?) rather than ANSI-92 wildcards (like % and _) in your criteria.

Solution 1Recommended

Disable ANSI-92 Query Syntax in Access Options

Turning off the ANSI-92 SQL syntax setting restores the standard Access SQL wildcard behavior, preventing the application from forcing the ALIKE keyword.

Microsoft Access supports two types of SQL syntax: the standard Access SQL and the ANSI-92 standard. When ANSI-92 is enabled, Access expects standard SQL Server wildcards. If you try to use the traditional LIKE operator with standard Access wildcards under this configuration, Access converts LIKE to ALIKE to enforce compatibility.

Open Access Options

Click on the 'File' tab in the top-left corner of the Microsoft Access ribbon, and select 'Options' from the bottom of the left-hand menu.

Navigate to Object Designers

In the Access Options dialog box, click on 'Object Designers' in the left-hand navigation pane.

Modify Query Design Settings

Scroll down to find the 'Query design' section. Under 'SQL Server Compatible Syntax (ANSI 92)', clear the checkboxes for both 'This database' and 'Default for new databases'.

Apply and Test

Click 'OK' to save your changes and close the options window. Open your query in Design View or SQL View, change 'ALIKE' back to 'LIKE', and run the query to ensure your wildcards work properly.

Disable ANSI-92 Query Syntax in Access Options

Query Syntax Updated: Once disabled, your database will use standard Access syntax. Remember to verify that any queries previously written with ANSI-92 wildcards (% and _) are updated to standard wildcards (* and ?).

Free Microsoft Office alternative

Looking for a Lightweight, Free Office Alternative?

While WPS Office does not include a database management tool like Microsoft Access, it is an exceptional, lightweight alternative for your Word, Excel, and PowerPoint needs. Enjoy full compatibility with Microsoft Office formats, a familiar user interface, and seamless document management without expensive subscription fees.

1. Download and Install: Visit the official WPS website and download the free WPS Office suite for your operating system.
2. Open Your Documents: Launch WPS Office and open your existing Word, Excel, or PowerPoint files with complete format compatibility.
3. Enjoy Seamless Productivity: Continue editing, saving, and sharing your documents using a familiar interface without a steep learning curve.

Fully compatible with Microsoft Word, Excel, and PowerPoint formats (.docx, .xlsx, .pptx).Lightweight installation with a familiar, easy-to-use tabbed interface.Includes a powerful built-in PDF editor and converter for daily document tasks.Free to use, allowing for a seamless migration from Microsoft Office for standard document editing.

microsoft office alternative - wps office

Frequently Asked Questions

What is the difference between LIKE and ALIKE in Access SQL?

In Access, 'LIKE' is used to match strings using standard Access wildcards (* and ?). 'ALIKE' is used to match strings using ANSI-92 standard wildcards (% and _) and works regardless of whether the global ANSI-92 setting is enabled in the database.

Why would someone enable ANSI-92 syntax in Access?

ANSI-92 syntax is typically enabled to maintain strict compatibility with Microsoft SQL Server and other standard SQL databases. This makes it easier to write queries that can be directly migrated to or run against external SQL tables.

Do I need to rewrite my queries after turning off ANSI-92?

Yes, if you wrote any queries using ANSI-92 wildcards (% and _) while the setting was enabled, you will need to manually update those queries to use standard Access wildcards (* and ?) for them to function correctly.

Office App Problems