query - function not available

[jancheta]

Hi! I have a sql statement:
SELECT * FROM tbl_ActiveFreight WHERE left([FILE_NO],2)="14";

On my PC, when I run the query it gives me an error:
Function is not available in expressions in query expression 'left([FILE_NO],2)="14"';

On another PC, the query works fine!?! On the PC that it doesn't work, I re-installed MSO 2000, didn't work, uninstalled MSO 2000 and installed again, still didn't work. This is so weird.

The only next step that I can think of is to start from scratch by re-formatting my HD, install Win2000 and MSO 2000.

Thanks in advance.

 

[WayneRyan]

jancheta,

Compare the references on the two machines.

Go to the database window, go to any module, design it,
then Tools --> References.

Your problem should be in there somewhere ...

Don't reformat etc. ...

hth,
Wayne

 

[PaulBricker]

First check the Reference Libraries on both machines. To do this, open any Module, new or existing, then go to the Toolbar and select Tools....References. Look at the machine that works first, and note all the libraries selected. Then check the bad machine and see if the Reference list matches. It sounds like you are missing a library. To select is, you just put a check mark in the box next to the library.

Paul

 

[jancheta]

Wayne and Paul... Thanks for your advise! The query went back to normal.

Here's what happened. In this application, I used the following references: Microsoft Commond Dialog Control, Calendar Control, and Outlook 9.0 Object Library. The thing is that I uninstalled Outlook 2000. And it appears that the MSO Uninstaller removed my Outlook 9.0 Object Library as well.When I went to my References, I saw:
Missing: Microsoft Outlook 9.0 Library
Then I unchecked the Reference, ran the query, and it worked fine.

What's really weird to me is that the error reported in the query doesn't have anything to do with my Outlook 9.0 Object Library. Which leads me to believe that if a Reference is missing, then Access may process things differently. In my case a simple basic query wouldn't run.

Thanks again for your advise. I hope other programmers or IT people chance upon reading this thread.

Regards,

Jason