Undefined function "format" in expression

[mithrush]

I am trying to run the following query MonthTxt: Format([monthnum]) which should change the number into text.

However when it runs I get an error Undefined function "format" in expression.

I have been into the trust centre area and amended the references but it still hasn't worked.

This started when I updated the database from 2003 to 2007.



Anybody any ideas?

Thanks

[cyanidem]

One idea - if you still have Access 2003 on the same system you may need to update reference in your database from Microsoft Office 11 Library to Microsoft Office 12 Library. It won't show as missing or broken but still may cause problems.

[pbaldy]

That error is normally associated with a reference problem. I know you said you checked them but perhaps this has more info:

http://www.accessmvp.com/djsteele/Ac...nceErrors.html

Also, have you tried including the second argument for the function, the desired format? Or using CStr() if all you're doing is making a number a string.

Welcome to the site by the way. I deleted your duplicate thread; there is no need to post the same question twice. The people who answer questions check all the forums.

[mithrush]

I tried those options but it still didn't work. if I resolve the first error I just keep getting more as the query runs about different expressions.

[June7]

Format function works without parameter for second argument.

Could post full SQL statement of attempted query. Although that probably won't be helpful since the query used to work.

Have you tried db on another computer running 2007?

[mithrush]

The query works within 2003 but I am trying to test all the macros, queries etc as we are in the process of moving to 2007. I don't want to convert all computers to 2007 until I am sure all everything works.

[cyanidem]

Could you show screenshot of references screen on that pc with 2007 where it's not working?

[mithrush]

I have read about references being the problem so I went in and there was one MISSING. utility. mda which I then un-ticked. this let me run the query without the error message until it got so far and then the error message appears again and then I go back into references this reference is ticked again.

Cant really get a screenshot of it

[pbaldy]

So it is a reference problem. That sounds like somebody's custom functions. Is the file where it's expected to be? The path should show in the references screen.

[cyanidem]

I'm quite convinced it is problem with reference being left from previous version of access and not showing as missing or broken but still "invalid" somehow. So update references to current access version should help.
Or there's an object somewhere in db named "Format", that could be anything from stupid label to table. Just a thought.