Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    3 Star Lounger
    Join Date
    Sep 2001
    Location
    Stuck at work..., Missouri, USA
    Posts
    248
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Can't use functions in queries (A97 Win2K)

    Anyone ever seen a problem where you can't use functions in queries?

    For example, I can't use UCase, Format, Left, etc...

    The inability to use them occured only after the upgrade from Win95 to Win2K.
    <font face="Comic Sans MS">Morgan Erickson</font face=comic>
    morgan.erickson@sprint.com
    <img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18>-From <img src=/S/flags/Colorado.gif border=0 alt=Colorado width=30 height=18> but living in <img src=/S/flags/Missouri.gif border=0 alt=Missouri width=30 height=18>...and working in Kansas.

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Can't use functions in queries (A97 Win2K)

    Have you checked for any MISSING references.
    To get to references, go into any code, then tools, references.
    Pat <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  3. #3
    3 Star Lounger
    Join Date
    Sep 2001
    Location
    Stuck at work..., Missouri, USA
    Posts
    248
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Can't use functions in queries (A97 Win2K)

    Checked them first thing...

    They are OK.
    <font face="Comic Sans MS">Morgan Erickson</font face=comic>
    morgan.erickson@sprint.com
    <img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18>-From <img src=/S/flags/Colorado.gif border=0 alt=Colorado width=30 height=18> but living in <img src=/S/flags/Missouri.gif border=0 alt=Missouri width=30 height=18>...and working in Kansas.

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Can't use functions in queries (A97 Win2K)

    What do you mean you cannot use functions in queries? Does it give an error message? Post your query here for us to have a look.
    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  5. #5
    3 Star Lounger
    Join Date
    Sep 2001
    Location
    Stuck at work..., Missouri, USA
    Posts
    248
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Can't use functions in queries (A97 Win2K)

    It's not on my PC so I can't give the exact error number & desc, but it something like "Function not available in query..."

    Any query along the lines of:
    SELECT UCase([FirstName]) FROM MyTable

    is failing...
    <font face="Comic Sans MS">Morgan Erickson</font face=comic>
    morgan.erickson@sprint.com
    <img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18>-From <img src=/S/flags/Colorado.gif border=0 alt=Colorado width=30 height=18> but living in <img src=/S/flags/Missouri.gif border=0 alt=Missouri width=30 height=18>...and working in Kansas.

  6. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Can't use functions in queries (A97 Win2K)

    I don't know, but maybe you will have to uninstall / install Access 97 again.
    Maybe the windows upgrade creamed something.
    I'm at a loss here, someone else?
    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

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

    Re: Can't use functions in queries (A97 Win2K)

    Approximate error messages are awfully hard to track down. Try to trigger the error and write down the exact error message. Then someone may be able to find it and figure out what's going on.
    Charlotte

  8. #8
    3 Star Lounger
    Join Date
    Sep 2001
    Location
    Stuck at work..., Missouri, USA
    Posts
    248
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Can't use functions in queries (A97 Win2K)

    I understand. This is not my PC, and the individual it is for is not the most technically savvy...

    I use a query to output some data to Excel via DoCmd.OutputTo acTable, "qryMyQuery", "MicrosoftExcel(*.xls)", "c:Test.xls", False, ""

    The query uses the CInt, Left, and Len functions. Here is the exact message:
    Function isn't available in expressions in query expression 'Cint(Left([Code],Len([Code])-1))'.

    This query worked before the user got upgraded to Win2K. The also got Office XP with the following exceptions:
    Kept Access 97
    Kept Excel 97

    Any pointers are greatly appreciated!
    <font face="Comic Sans MS">Morgan Erickson</font face=comic>
    morgan.erickson@sprint.com
    <img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18>-From <img src=/S/flags/Colorado.gif border=0 alt=Colorado width=30 height=18> but living in <img src=/S/flags/Missouri.gif border=0 alt=Missouri width=30 height=18>...and working in Kansas.

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Can't use functions in queries (A97 Win2K)

    This really sounds like a References problem. I know that Patt has already asked you about that. Just to make sure: did you look at Tools/References... on the PC of the user who has the problem?

  10. #10
    3 Star Lounger
    Join Date
    Sep 2001
    Location
    Stuck at work..., Missouri, USA
    Posts
    248
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Can't use functions in queries (A97 Win2K)

    Absolutely. That's the first thing I checked.

    In addition, I changed her installation of Access to include all options, in case something was missed in the initial install. (This has worked for me with other issues in the past)

    This one REALLY has me stumped. It's tough to tell her "It works for me just fine..." when it won't for her.

    It's not just her PC, either. It's everyone in her group that is using it. They all have similar PC configurations.

    Could there be a problem with the Corporate image of Win2K that is causing it? How about Excel 97 being in the mix with some Office XP?

    Sign me Extremely Frustrated,
    <font face="Comic Sans MS">Morgan Erickson</font face=comic>
    morgan.erickson@sprint.com
    <img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18>-From <img src=/S/flags/Colorado.gif border=0 alt=Colorado width=30 height=18> but living in <img src=/S/flags/Missouri.gif border=0 alt=Missouri width=30 height=18>...and working in Kansas.

  11. #11
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    New Jersey, USA
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Can't use functions in queries (A97 Win2K)

    I have had this problem several times It seems to happen for no apparent reason. It took a long time but we finally found the solution.


    In my case it was a result of a missing activex addin - "mscal.ocx "activex
    Here is how we fixed it.


    install "mscal.ocx" Activex add in
    close all forms of the database
    open modules
    select new
    tools/references
    select an unselected reference, noting the one you pick so you can reselect it later. Close and reopen
    unselect the reference from above

    These are order sensitive. Do not do it out of sequence or the problem will remain.

    I know these steps sound crazy but we got this off of the microsoft website, and it worked!
    Good Luck

  12. #12
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Can't use functions in queries (A97 Win2K)

    That is the Calender control. So it seems that the upgrade to Win2k could have caused that problem.
    I have a Win95 machine and I have found Mscal.ocx and its associates in 2 directories, c:WindowsSystem and c:Program filesMicrosoft OfficeOffice.
    Strange one that !!
    Pat <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  13. #13
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    New Jersey, USA
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Can't use functions in queries (A97 Win2K)

    I have found this problem on new machines and on existing machines. We just had it happen on several existing machines that use the same databases, and we did not do anything to cause it.(At least that anyone will admit to!) It just happened one Monday. Now I know just to go in and refresh the modules.

    Also, check and see if the now() works. I could always get the now() to work but not the date() when I had this problem. Just curious.

  14. #14
    3 Star Lounger
    Join Date
    Sep 2001
    Location
    Stuck at work..., Missouri, USA
    Posts
    248
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Can't use functions in queries (A97 Win2K)

    That is the ABSOLUTE WEIRDEST thing I have EVER seen in my life...

    Oddly enough, it worked.

    I appreciate the help. File that one under Odd Things You Might Try...

    Thanks a TON!!
    <font face="Comic Sans MS">Morgan Erickson</font face=comic>
    morgan.erickson@sprint.com
    <img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18>-From <img src=/S/flags/Colorado.gif border=0 alt=Colorado width=30 height=18> but living in <img src=/S/flags/Missouri.gif border=0 alt=Missouri width=30 height=18>...and working in Kansas.

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

    Re: Can't use functions in queries (A97 Win2K)

    Office 97 placed the calendar control in a different location than later versions of Office. If you wind up with a mismatch between the dll and the ocx, you wind up with problems.
    Charlotte

Page 1 of 2 12 LastLast

Posting Permissions

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