Results 1 to 14 of 14
  1. #1
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,852
    Thanks
    4
    Thanked 259 Times in 239 Posts

    Functions not available - Access 2000

    I have a very simple query that causes errors on machines that have Access 2000 installed fresh. On at least 3 machines that had Access97 which then were upgraded to Access 2000 the query works fine.

    Both the Mid function and so the Format function cause the error. I have attached a graphic of the error message and here is the query itself.
    SELECT tblJobs.PK, Mid([RefNum],3,4) AS Ref, Format([DateStart],"yyyy") AS [Year]
    FROM tblJobs;

    Is there an option I need to set to make this work or can't functions be used in Access queries anymore?
    Attached Images Attached Images
    Andrew Lockton, Chrysalis Design, Melbourne Australia

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

    Re: Functions not available - Access 2000

    This is commonly caused by broken references. You may have a reference set to an Office 97 library that doesn't exist on the Office 2000-only machines. In the VBE, go into Tools-->References and look for the MISSING items. You may be able to substitute the Office 2000 equivalents and then replace the copies on all machines with the ones that work in 2000.
    Charlotte

  3. #3
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,852
    Thanks
    4
    Thanked 259 Times in 239 Posts

    Re: Functions not available - Access 2000

    Charlotte,

    The interesting thing is that the References list didn't show anything missing. As an odd way to fix it I saved the database back to Access97 format and merely doing so fixed the references in the 2000 version. So doing a save back in format resolved the missing reference wherever/whatever it was.

    On one of the machines when I saved back a version, a dialog said I was missing a reference. On another machine it didn't. On both the original file started working. [img]/S/aflame.gif[/img] Nothing like consistency.
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  4. #4
    New Lounger
    Join Date
    Jan 2001
    Location
    New Zealand
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Function not available in Query

    I too have had the error message 'Function Isn't Available in Expressions in Query Expression'. It was caused by having different versions of the comctl32.ocx file on the development machine than on machines where the database was distributed. The problem can also occur any time new software you install overwrites the ocx file. It may happen with other ocx files as well.
    A simple work around is to distribute the same version of the ocx file to all machines or if it is not an mde file then removing and re-establishing the reference to the ocx file should fix it.

    The following Knowledge base article describes the problem and has all the answers:
    Article ID:Q194374 - ACC97: Error Message: Function Isn't Available in Expressions in Query Expression,
    <A target="_blank" HREF=http://support.microsoft.com/support/kb/articles/Q194/3/74.ASP>http://support.microsoft.com/support/kb/ar...s/Q194/3/74.ASP</A>
    Hope this helps for future reference.

    Simon

  5. #5
    Gold Lounger
    Join Date
    Dec 2000
    Location
    Hollywood (sorta), California, USA
    Posts
    2,759
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Functions not available - Access 2000

    Andrew,

    Geoff posted your post in the VBA forum which is where I saw it. I have an idea. Since you are developing in Access 2000, the database engine [could be] | [is] | [is not] MSDE -- the SQL Server engine.

    I don't know if Access 2000 allows you to select JET or MSDE, but if you are using MSDE, then the query syntax is based on the SQL-TransAct language. In that language there is no MID function -- at least there wasn't back in SQL 6.x (please correct this if incorrect). There is a SUBSTRING function, however, that accomplishes the same thing. How about trying the query with SUBSTRING instead of MID?

    Just a thought.
    Kevin <IMG SRC=http://www.wopr.com/w3tuserpics/Kevin_sig.gif alt="Keep the change, ya filthy animal...">
    <img src=/w3timages/blackline.gif width=33% height=2><img src=/w3timages/redline.gif width=33% height=2><img src=/w3timages/blackline.gif width=33% height=2>

  6. #6
    Gold Lounger
    Join Date
    Dec 2000
    Location
    Hollywood (sorta), California, USA
    Posts
    2,759
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Functions not available - Access 2000

    p.s. How do you get that nice textured background to the dialog?
    Kevin <IMG SRC=http://www.wopr.com/w3tuserpics/Kevin_sig.gif alt="Keep the change, ya filthy animal...">
    <img src=/w3timages/blackline.gif width=33% height=2><img src=/w3timages/redline.gif width=33% height=2><img src=/w3timages/blackline.gif width=33% height=2>

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

    Re: Functions not available - Access 2000

    SQL Server 2000 supports functions, but not SS 6.5 or 7.0
    Charlotte

  8. #8
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,852
    Thanks
    4
    Thanked 259 Times in 239 Posts

    Re: Functions not available - Access 2000

    Texturing is easy if the 24bit screen capture gets converted to a GIF with dithering.
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  9. #9
    Gold Lounger
    Join Date
    Dec 2000
    Location
    Hollywood (sorta), California, USA
    Posts
    2,759
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Functions not available - Access 2000

    I'm referring to the builtin functions. I have the TransAct SQL reference for 6.5 right here and it's got all kinds of functions: aggregate, date, math, string, system and the mother of all function types: Niladic!

    This is a typical use of the SUBSTRING function:

    Select LastName, substring(firstname,1,1) from employees

    What do you mean by 6.5 and 7 don't supports functions?
    Kevin <IMG SRC=http://www.wopr.com/w3tuserpics/Kevin_sig.gif alt="Keep the change, ya filthy animal...">
    <img src=/w3timages/blackline.gif width=33% height=2><img src=/w3timages/redline.gif width=33% height=2><img src=/w3timages/blackline.gif width=33% height=2>

  10. #10
    Gold Lounger
    Join Date
    Dec 2000
    Location
    Hollywood (sorta), California, USA
    Posts
    2,759
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Functions not available - Access 2000

    What I meant was what do you have to do to make the msgbox dialog form look different from the default. It's easy to change a userform backgroup, but the built-in msgbox dialog? Revealeth thy secret oh one of great knowledge...
    Kevin <IMG SRC=http://www.wopr.com/w3tuserpics/Kevin_sig.gif alt="Keep the change, ya filthy animal...">
    <img src=/w3timages/blackline.gif width=33% height=2><img src=/w3timages/redline.gif width=33% height=2><img src=/w3timages/blackline.gif width=33% height=2>

  11. #11
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,852
    Thanks
    4
    Thanked 259 Times in 239 Posts

    Re: Functions not available - Access 2000

    Use the force Kevin!

    That dialog is simply a cut and paste of the infernal dialog that Microsoft kinldy generated for me to inform me of my own inadequacies. I was on a Win2000 machine using Access2000 and I can only guess that the engine for this lounge converted the otherwise plain background bitmap into a dithered GIF using a standard palette for display here. If you right click the image you will see it is a GIF and I sure didn't do that.

    It would save considerable disk space if Eileen could set the GIF creator to save non-dithered images instead but you would then get banding and colour shift problems that may annoy instead.

    In short, I did nothing to make the dialog appear textured, nobody saw me, you can't prove a thing. (apologies to Bart)
    Andrew Lockton, Chrysalis Design, Melbourne Australia

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

    Re: Functions not available - Access 2000

    Yes, and I should have specified user-defined functions, which is what SQL Server considers any functions defined outside of SQL Server, including those built into Access.
    Charlotte

  13. #13
    Gold Lounger
    Join Date
    Dec 2000
    Location
    Hollywood (sorta), California, USA
    Posts
    2,759
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Functions not available - Access 2000

    Gee wiz, I thought maybe you had a slick way to clobber the builtin dialog background. That would be pretty nice. Put the company logo in watermark or off to the side on all the msgbox dialogs. I think that would impress the customer to no end.

    ANYWAY! what about your query? Speaking of "builtin", what happens if you use the builtin SubString function instead of MID in the query? I'm dying to know if it works.
    Kevin <IMG SRC=http://www.wopr.com/w3tuserpics/Kevin_sig.gif alt="Keep the change, ya filthy animal...">
    <img src=/w3timages/blackline.gif width=33% height=2><img src=/w3timages/redline.gif width=33% height=2><img src=/w3timages/blackline.gif width=33% height=2>

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

    Re: Functions not available - Access 2000

    Kevin,

    I don't think it was ever established that SQL Server was anywhere in this picture. Substring is not a VBA function, so it do nothing in Access except give you an error message.
    Charlotte

Posting Permissions

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