Results 1 to 7 of 7
  1. #1
    Lounger
    Join Date
    Feb 2011
    Posts
    43
    Thanks
    0
    Thanked 6 Times in 6 Posts

    In-built Access functions not working in queries

    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.

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    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.
    Regards
    John



  3. #3
    Lounger
    Join Date
    Feb 2011
    Posts
    43
    Thanks
    0
    Thanked 6 Times in 6 Posts
    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

  4. #4
    4 Star Lounger
    Join Date
    Apr 2001
    Location
    Guatemala City
    Posts
    515
    Thanks
    0
    Thanked 0 Times in 0 Posts
    How do you handle the non-existence of a reference? My Access 2003 shows a missing utility.mda, but I can't fine it.

  5. #5
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    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.
    Regards
    John



  6. #6
    4 Star Lounger
    Join Date
    Apr 2001
    Location
    Guatemala City
    Posts
    515
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by johnhutchison View Post
    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.

  7. #7
    Lounger
    Join Date
    Feb 2011
    Posts
    43
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Follow up

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •