Access built-in functions like Year() and Date() are not recognized

Question

I tried the simple query:

select Year ( #5/1/1990# );

It shows error dialog:

Not defined function "Year" in expression

Also, when I use Date() in the form it shows #NAME. I added it with expression builder in Access. What am I doing wrong?

Answer 1

There is a problem with the referenced libraries in your project.

Open the VBA Editor ("Database Tools"->"Visual Basic" in the Ribbon) there go to the menu "Tools"->"References". Check if there is any library marked with "missing". Reinstall those libraries or change their path by removing them and adding them using the "Browse" button.

Generally you should remove any library you do not actually use in your project.

If that does not help, remove all the libraries (if possible, some are built-in and can not be removed) and add them again.

After changing the libraries use the menu "Debug"->"Compile yourProject". That should show error messages if there are obvious problems with the libraries in the VBA code. It will however not detect any problems with expressions used in queries or Form-controls.

If none of the steps above helped fixing the problem. You could create a new Access database and then import all the objects from your old database into the new one unsing "External Data"->"Access" in the Ribbon.

Answer 2

I just renamed my database after dealing with my database not running any queries that had expressions. Try copying and renaming your database first.

Answer 3

I had a similar problem where all built-in functions stopped working in a database. All the references checked out fine.

Accidentally, it resolved by renaming the database so I could restore an old version. When re-opening the renamed database I was asked to make it trusted. After that, the built-in functions worked again.

Answer 4

Thank you for the posts. I tried a new empty database: simple table with a simple query and everyhting worked! I will create a new Database and copy my stuff into it.