In-built Access functions not working in queries

Topic

New Reply

Greetings all

In recent months, problems are arising when running queries which have embedded in them, in-built functions, such as Left, Format or Date().

Queried that were running happily in applications for several years are now giving an error such as “Run Time error 3085: Undefined function ‘format’ in expression.

I can work around the problem by replacing the in-built function with a call to function eg Function DoFormat(str as string) with the in-built function embedded.

Has anyone else encountered this behaviour? Keep in mind that different applications are involved at different sites and it does not happen to all workstations at the one site using the same shared MDB file.

Cranky Cronk.

Reply | Quote

Replies

This is usually caused by broken References.
Press Alt +F11 to activate the VBA editor, then go to Tools..References. Look for some of the ticked ones that show “Missing“. You need to replace them with a version present on that computer. usually means a different version number.

Reply | Quote

Thanks John for replying.

There are indeed References, mainly to Office Libraries. There just may be a connection between References not being “fixed” when the mdb is copied on to a later version Office, and yes, I know this definitely will not happen copying to a PC with an earlier version of Office.

Not so cranky Cronk

Reply | Quote

How do you handle the non-existence of a reference? My Access 2003 shows a missing utility.mda, but I can’t fine it.

Reply | Quote

The referece to utility.mda is a relic from a much older version of Access. If you just untick it, I think you will the db works OK.

Reply | Quote

The referece to utility.mda is a relic from a much older version of Access. If you just untick it, I think you will the db works OK.

Thanks. That did the trick.

Reply | Quote

I seem to have resolved my issue by removing the reference to a Date Calendar mde control. Maybe there was some incompatibility with Access 2007.

Happy Cronk

Reply | Quote