Access 2013 Undefined function 'Format' in expression

Question

I have created an access database which is used to create reports by various people. The database simply links to various external data sources. Via macros, it runs queries, creates reports and pushes them to a SharePoint site. The macro is triggered by a button. It runs fine for me. No errors and works perfectly. For another user in my department it doesn't work. She gets the following error:

Undefined function 'Format' in expression

I looked and the macro fails on an update query that runs. See the below screenshot.

It doesn't like the "Format" portion of the expression, but only on her PC. It works fine on mine. I don't use 'Format" for any other object in my database so I'm not sure why it would error out. I use the Format function all the time to format date values.

Any idea why this is selectively erroring out on one PC?

Solution 1

Same problem but in Access 2016, I didn't have any references reported as 'Missing' but I found adding in the following reference made it work:

C:\Program Files (x86)\Common Files\Microsoft Shared\VBA\VBA6\VBE6EXT.OLB

Solution 2

Problem: There is Reference List in VBA. Once you open (VBA environment-> Tools-> Reference), you will find a Checked mark in front of “MISSING”. Solution: Uncheck that check Box which says “MISSING”, and save it. Result: It will work

Comments

[abu]

Thanks @FrinkTheBrave , the same happened to me. How did you know which reference would make it work? The odd thing is I the "undefined function" error only happened in one PC (with Office 365) but not in the other (Office 2016), despite both had exactly the same references (I only had to add VBE6EXT.OLB in the one with Office 365). Once I added the file, the new reference name was "Microsoft Visual Basic for Applications Extensibility 5.3"