Function Problems

Fix Access IIF, LEFT, and RIGHT Syntax Errors

Aamir Naveed AkramAamir Naveed Akram Nov 3, 2026 868 views

Question details

Users encounter syntax errors when executing basic functions such as IIF, LEFT, and RIGHT in Microsoft Access query expressions.

How to Fix Syntax Errors in Access IIF, LEFT, and RIGHT Functions
Product
Microsoft Access
Device & OS
Windows 64-bit
Scenario
Running queries or expressions containing built-in functions like IIF, LEFT, or RIGHT in a 64-bit Office environment.
Observed behavior
The system unexpectedly returns a syntax error, even for properly formatted and simple expressions like IIF(1+1=2, "yes", "no").
Before you start

Before making changes to the VBA Editor, save a backup copy of your Access database (.accdb) to prevent any accidental code or data loss.

Solution 1Recommended

Resolve Missing or Broken VBA References

This is the most common cause for built-in Access functions failing. Clearing broken references restores standard function availability.

When an Access database is moved between different computers or upgraded to a 64-bit version of Office, the library references can break. If a library is marked as MISSING, Access fails to recognize basic string and logic functions.

1
Open the Visual Basic Editor

With your database open in Access, press ALT + F11 on your keyboard to launch the Visual Basic Editor.

2
Access the References Menu

In the top menu bar, click on 'Tools' and select 'References' from the drop-down list.

3
Clear Missing Libraries

Look through the list of checked libraries at the top. If you see any items starting with the word 'MISSING:', uncheck the box next to them.

4
Select the Correct Object Library

Scroll down the list and check the box for the correct Microsoft Office or Access Object Library that corresponds to your current installed version, then click 'OK'.

5
Compile the Database

Go to the 'Debug' menu at the top and select 'Compile Database'. If no errors pop up, your references are fixed and the functions will work correctly.

Resolve Missing or Broken VBA References
Library Reconnection: Compiling the database after fixing references ensures that all queries and VBA modules are properly synchronized with the updated libraries.
Free Microsoft Office alternative

Looking for a Lightweight Alternative to Microsoft Office?

While Microsoft Access manages highly complex database architectures, WPS Office provides a complete, free, and lightweight suite for your everyday document, spreadsheet, and presentation needs. Experience familiar interfaces and robust features without the heavy subscription fees.

  1. 1. Download the Installer: Visit the official WPS Office website and download the free installation package.
  2. 2. Install the Suite: Run the installer to set up WPS Writer, Spreadsheet, Presentation, and PDF tools on your PC.
  3. 3. Open Your Files: Instantly open and edit your existing Microsoft Office documents with seamless formatting retention.
Fully compatible with Microsoft Excel (.xlsx), Word (.docx), and PowerPoint (.pptx) formats.Lightweight installation that runs smoothly on both 32-bit and 64-bit Windows systems.Intuitive tabbed interface that makes managing multiple documents effortless.Built-in advanced PDF editing and conversion tools at no extra cost.
microsoft office alternative - wps office

Frequently Asked Questions

Why does Access say 'MISSING' in my VBA References?

This happens when a database created on a machine with a specific version of an object library is moved to a machine where that exact library version is unavailable, uninstalled, or upgraded to a different architecture like 64-bit.

Do I need to know VBA programming to fix these syntax errors?

No advanced VBA coding is required. You simply need to open the Visual Basic Editor (ALT + F11), navigate to the References menu, and uncheck the missing library to resolve the conflict.

Will changing these references break my existing queries?

No, removing a broken or 'MISSING' reference and replacing it with the correct local library actually restores your queries. It ensures that built-in functions like IIF, LEFT, and RIGHT work as intended.

Can I use WPS Office to open Microsoft Access databases?

WPS Office focuses heavily on word processing, spreadsheets, presentations, and PDFs. It does not include a database management tool natively, so it cannot open .accdb or .mdb files directly.