Results 1 to 12 of 12
  1. #1
    New Lounger
    Join Date
    Jan 2003
    Location
    Albury, New South Wales, Australia
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Function Not Available in expresions in query (9.0.2720)

    I have installed the database on the clients system and when I have called up the main form it comes up with the following message:

    Function is not available in expressions in query expression 'UCase([CompanyName])&""&UCase([ClientSurname])&" "&IIf([ClientChristianName] Is Not Null, [ClientChristianName]&" "&[ClientMiddleNames])'.

    I don't have any problems with it on my pc.

    My version is 9.0.2720 and my clients version is 3821.SR-1. We think this could be the problem. I'd like to know what I have to do from here. Can he convert it? Can I download anything to bring me up to his version, and if so, what, when, where and how. I have never encountered this problem before.
    Any assistance would be greatly appreciated.

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Function Not Available in expresions in query (9.0.2720)

    Most often, this kind of error is caused by a missing reference - check on your clients system and make sure the all is OK - i.e. the same as on your PC. (If you've not played with that, it is reached via the VBA editor, opening a module in design view, and then selecting Tools/References. You should really upgrade to 9.0.3821 (SR-1A) as it fixes several things that were bugs in the initial release - one of the nastier was a problem with autonumber fields where it would sometimes give two records the same number. SR-1A is available as a download from MS if you have a broadband connection, or you can order a CD for a nominal charge.
    Wendell

  3. #3
    New Lounger
    Join Date
    Jan 2003
    Location
    Albury, New South Wales, Australia
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Function Not Available in expresions in query (9.0.2720)

    Thanks Wendell for your reply. Leads me to a couple of related things:
    1. If I upgrade will this query work (ie, will it recognise UCase?), and 2
    2. When I go into Tools/References, exactly what am I looking for to make sure everything is okay?

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Function Not Available in expresions in query (9.0.2720)

    1. Theoretically it should, but there aren't any guarantees in this business. The client may have an older version of one of the DLLs used by Access, or some other weird problems that are causing the functions not to work. It's one of the more common problems with A2K - we didn't see much of it with prior versions. Things that can cause this sort of thing include installing the developer desktop version of SQL Server, installing incompatible versions of the Microsoft Data Access Controls, and a myriad of other fairly remote possibilities.
    2. It will display "missing" for one or more of the referenced items. BTW, if you don't have any code modules, you can still check references by creating a new module with no code, and then do Tools/References. In the dialog box you can click on each of the listed references and it will show you where it's pointing.

    Just out of curiosity, is this a database that was developed in A97 and then converted to A2K?
    Wendell

  5. #5
    New Lounger
    Join Date
    Jan 2003
    Location
    Albury, New South Wales, Australia
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Function Not Available in expresions in query (9.0.2720)

    Thanks Wendell. I'll give it all a go.
    I'm feeling like quite a novice here, can you tell me what BTW stands for?
    Also in answer to your question, the database was developed in A2K.

  6. #6
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Function Not Available in expresions in query (9.0.2720)

    Hi Victoria,
    Don't worry about feeling like a novice - we've all been there. BTW is shorthand for By The Way - there are some others like IMHO (in my humble opinion) and FWIW (for what it's worth). Many of us are lazy typists and use some of those shorthand expressions - on the other hand some use smilies <img src=/S/smile.gif border=0 alt=smile width=15 height=15> to make a point. There are lots of them in the smilies panel that you can bring up once you start a message or a reply.

    Since the database was developed in A2K, the references issue is a little bit less probable, but the fact that function calls don't compile indicates one or more of the libraries is probably missing, or you have a DLL incompatibility. Carefully compare the references list on your PC with the client PC and look for any differences. It can occasionally be an improperly registered component as well. Good luck.
    Wendell

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Function Not Available in expresions in query (9.0.2720)

    Don't forget AFAIK (As Far As I Know) in that list , Wendell. It pops up regularly in posts as well.
    Charlotte

  8. #8
    New Lounger
    Join Date
    Jan 2003
    Location
    Albury, New South Wales, Australia
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Function Not Available in expresions in query (9.0.2720)

    Thanks again.
    I am meeting with my client later this afternoon to check his references.
    Just one more question (then I'll leave you alone for a while!), what do I do if there is a DLL incompatibility?

  9. #9
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Function Not Available in expresions in query (9.0.2720)

    Hopefully you'll get this in time - should still be morning there:
    The first challenge is to determine if there is one. If you are using any ActiveX controls such as the Calendar control, the Treeview, and similar things, then check the versions of Calendar.ocx and mscomctl.ocx. If not, it's a bigger challenge, and one that would take a fair bit of investigation. Hopefully it will be a missing reference. One other thing that occurs to me is to decompile the database and then try to compile it. That will sometimes solve weird problems - see <post#=44732>post 44732</post#> for how to do that. If you have web access from the client and get stuck, someone here can try to help.
    Wendell

  10. #10
    New Lounger
    Join Date
    Jan 2003
    Location
    Albury, New South Wales, Australia
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Function Not Available in expresions in query (9.0.2720)

    Thanks once more Wendell.
    I think I'm armed with enough now to confidently do something about it.
    I'll let you know how it all goes.

  11. #11
    New Lounger
    Join Date
    Jan 2003
    Location
    Albury, New South Wales, Australia
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Function Not Available in expresions in query (9.0.2720)

    Hello Wendell

    Sorry about the delay in letting you know how things went - everything has just been a process of elimination.
    Decompiling and recompiling on my pc worked ok.
    Loading the patches was a problem - not enough disk space or corrupt cabinet file. Not upgrading hardware until mid next year.
    One of the aspects that concerns me about all of this is that when the database was first loaded it didn't prompt to convert the database to his version. I expressed my concerns about experiencing conflicts with my other clients and in the end it was decided that he would go back to the same version as myself.
    A satisfactory conclusion? Perhaps not but a workable one.
    Thanks for all your expertise on this matter - I have certainly expanded my knowledge base.
    Have another question about a date field that will automatically increment - will put that on the open forum and leave you alone for a while!!

    Thanks again.

  12. #12
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Function Not Available in expresions in query (9.0.2720)

    I wouldn't expect any sort of prompt to convert the database as long as you are both running Access 2000. The only real difference between his version and yours is that he has fixes to a number of problems that were detected in the initial release of Access. Thus installing the service releases and service packs (there are now 3) would probably be a good idea for everyone involved. There are however some aspects that should be considered if users are dependent on Outlook - check out the info on Woody's Office Watch about 3 months ago. You might also take a look at some of the threads in the General Office Solutions forum.
    Wendell

Posting Permissions

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