SELECT [test 1].string, Format([number],"000000000") AS cd FROM [test 1];
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
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.
\\SomeComputer\Dir1\Dir2\Databases\my.mdbOur 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
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