Can’t use functions in queries (A97 Win2K)

Topic

New Reply

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.

Reply | Quote

Replies

Have you checked for any MISSING references.
To get to references, go into any code, then tools, references.
Pat

Reply | Quote

Checked them first thing…

They are OK.

Reply | Quote

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

Reply | Quote

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…

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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!

Reply | Quote

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?

Reply | Quote

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,

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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!!

Reply | Quote

I have had this problem several times as well. Once was when converting an Access97 DB to Access2k (the

Reply | Quote

(Edited by charlotte on 20-Nov-02 20:06. to activate link)

http://support.microsoft.com/default.aspx?…kb;en-us;194374%5B/url%5D

Reply | Quote