Function is not available in expressions in query expression RRS feed

  • 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.



    Tuesday, May 9, 2017 7:44 PM

All replies

  • 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)

    Tuesday, May 9, 2017 8:23 PM
  • 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?


    Tuesday, May 9, 2017 9:06 PM
  • 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

    Tuesday, May 9, 2017 10:02 PM
  • 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.



    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 MSDNFSF@microsoft.com.

    Wednesday, May 10, 2017 3:35 AM
  • 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.
    Wednesday, May 10, 2017 12:07 PM
  • Yes as I mentioned there are no missing references.
    Wednesday, May 10, 2017 12:07 PM
  • See my previous response on references and copies.
    Yes I did compile without errors.


    Wednesday, May 10, 2017 12:09 PM
  • 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?


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Wednesday, May 10, 2017 12:19 PM