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

- 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 making changes to the VBA Editor, save a backup copy of your Access database (.accdb) to prevent any accidental code or data loss.
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.
With your database open in Access, press ALT + F11 on your keyboard to launch the Visual Basic Editor.
In the top menu bar, click on 'Tools' and select 'References' from the drop-down list.
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.
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'.
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.

Enforce Explicit Variable Declaration
Using 'Option Explicit' forces you to declare all variables, helping you quickly identify if a syntax error is due to an unrecognized element rather than a broken library.
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. Download the Installer: Visit the official WPS Office website and download the free installation package.
- 2. Install the Suite: Run the installer to set up WPS Writer, Spreadsheet, Presentation, and PDF tools on your PC.
- 3. Open Your Files: Instantly open and edit your existing Microsoft Office documents with seamless formatting retention.

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.




