Link to home
Create AccountLog in
Avatar of pcalabria
pcalabriaFlag for United States of America

asked on

"Function is not available in expressions in query expression" Error Message

Hello experts,

I am trying to run a query which selects all the records in which the first 5 characters of a field called "Status" are "Quote".

I've included the following phrase in my select query:  Left([Status],5)="Quote" and am getting the subject error message.

The interesting thing is that when I run exactly the same mdb file on a different machine no errors are created and the machine runs as expected.

For example, lets take two of my machines which are on the same network.

Both are Dell 3000 running windows XP Pro SP3.
Both are running MS Access 2000 (9.0.3821 SR-1)
Both are running exactly the same .mdb file

The first machine, however, gives me the subject error.
The second machine works as expected.

Can anyone help?

Complete Query:
strSQLtext = "SELECT Quotes.SearchNumber, Quotes.PartNumber1, Quotes.WantQuantity1, Quotes.StatusDate, ComponentSlave.SnapShotDate, Quotes.Status "
strSQLtext = strSQLtext & "FROM Quotes LEFT JOIN ComponentSlave ON Quotes.SearchNumber = ComponentSlave.SearchNumber "
strSQLtext = strSQLtext & "where (((ComponentSlave.SnapShotDate) < #" & dteLastSnapShotDate & "# Or (ComponentSlave.SnapShotDate) Is Null) And ((Quotes.StatusDate) >= #" & dteRequestDate & "#) And ( (Left([Status], 5)) = 'Quote') ) "
strSQLtext = strSQLtext & "ORDER BY Quotes.StatusDate DESC ;"
ASKER CERTIFIED SOLUTION
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
agree with Rey, my first inclination is a missing reference.

Although it should not be necessary, you might also want to modify the reference to [Status] in your WHERE clause to Quotes.[Status]
Avatar of pcalabria

ASKER

Thanks guys, Rey was right-on.

Kind of weird, it was a reference to an outlook file MSOUTL.olb file that was missing.
Not weird because the reference was missing... that was my fault.. but because it was causing that error.

Looks like Right and Left don't work without the outlook module?
I'll never understand some of this stuff...

Thanks Dale for the suggestion... I usually try to remove the table name from my code to simply it...
I also try not to use field names like Status... but sometimes I  f-up... :)
<Looks like Right and Left don't work without the outlook module?>
not really, it could be any other library that is missing and can cause the same error.