Function is not available in expressions in query expression

Question

We have a group of PC's all running Windows 10 and Office 365. We have an Access 2016 front end that links to SQL database. On about 3 of the PC's they get the error Function is not available in expressions in query expression when it hits various functions in a query. It could be Left() or Date(). We were able to get around the Date() function by changing it to Now() but now the Left() function is coming up. Everyone uses the same copy of Access front end. The PC's getting the error do not have any references missing so that is not the problem. At a total loss and not in a position to pay Microsoft $499 to troubleshoot what may be their issue. Any help is greatly appreciated.

Thanks

Greg

Answer 1

Did you check the References in the database project to make sure that none of them are missing? This can cause the built-in VBA function library to not be recognized.

---

Paul ~~~~ Microsoft MVP (Visual Basic)

Answer 2

Everyone uses the same copy of Access front end.

Hi Greg,

If you're saying all users are opening the same physical file from a network location, then it might be contributing to the problem. Best practice is to have each user save their own copy of the front end on their local machine to minimize problems.

Can you please confirm each user is opening their own copy of the front end?

Thanks.

Answer 3

Greg,

You must never share a common copy of the front-end.  Each user needs to have their own individual local copy of the front-end.  You may wish to take a quick look at http://www.devhut.net/2017/04/09/setting-up-an-ms-access-database/ for other general setup guidelines.

Furthermore, the symptoms you describe are typical of a reference problem.  If they have the full version of Access you can quickly validate this by going into the VBE and then Tools -> References ... And looking to see if any are marked as MISSING.

Is everyone running the exact same version of Access?

Did you ensure that your database compiled without errors?

---

Daniel Pineault, 2010-2016 Microsoft MVP
Professional Support: http://www.cardaconsultants.com
MS Access Tips and Code Samples: http://www.devhut.net

Answer 4

Hi Greglh10290,

to solve the issue temporary , you can try to create your own function and then call that inbuilt function in your function and use your function in query.

by this way it can start working.

you can try to check that whether you had added reference to any Date control.

if yes then try to remove the reference and try to check again.

also try to check that whether you added reference to any old library or unsupported version of library.

if you find that then try to add reference to correct library.

also try to check the suggestion given by others and let us know if that solves your issue or not.

Regards

Deepak

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 5

I wrote a C# front end that will automatically download a revised copy of the front end whenever the program is started so everyone works from the same copy of the front end. They do not work on the same file from a network location.

Answer 6

Yes as I mentioned there are no missing references.

Answer 7

See my previous response on references and copies.
Yes I did compile without errors.

thanks

Answer 8

Yes as I mentioned there are no missing references.

Sorry I skimmed past that sentence. Is Visual Basic For Applications the highest (priority) in the list of References? Do you encounter the same issue in VBA code or is it just the query expressions? Is the database trusted? Also, is Sandbox Mode disabled by any chance?

https://support.office.com/en-us/article/Functions-and-properties-in-Access-blocked-by-sandbox-mode-9a829783-f7a8-4a9f-8d43-8650b8cc9565

---

Paul ~~~~ Microsoft MVP (Visual Basic)