Databases - MS Access "Format" Problem

Normally, MS Access queries can accept a variety of commands. One I frequently use is format.

However, I have seen a case where the query worked fine one day, and produced errors the next.

As a test, I created identical tables and queries in 2 different mdb files. In one case, the format worked as expected, in the other, the result was "#Error". Oh, did I mention that both mdb files were on the same machine?

Repair Database had no effect.


Solution

Well, thanks to the newsgroup microsoft.public.access.queries, the problem is solved.
It sounds like a broken Reference after a DB conversion to a newer version. Click on Modules/Design/Tools/References and look for a missing. If not click the checked boxes off and back on then save and try again. I think I remembered this right there were some recent posts on Left,Right,Mid etc. not working and this was the problem.
The mdb file was on a server and a couple of modules were linked via a full path, not a drive letter. Our IT staff decided to rename SomeComputer to SomeOtherComputer. Of course that broke the link. Well I fixed the link ... and that's when the problem started.

The solution was to

Then the query worked as before - ie, the Format command now works.

Simply repairing the link did not work, neither did simply un-checking and re-checking the link ... the link had to be dropped and then re-added (ie, you had to perform a save - click OK - after un-checkeing the link).

I don't think I would have figured that out on my own. Format is an MS Access command and is not implemented in the linked mdb file that I wrote. However, it was MY file that had to be dropped to fix the problem.


Author: Robert Clemenzi - clemenzi@cpcug.org
URL: http:// cpcug.org / user / clemenzi / technical / Databases / MSAccess / FormatProblem.html