Avatar of pcalabria
Flag 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 ;"
Microsoft Access

Avatar of undefined
Last Comment
Rey Obrero (Capricorn1)

8/22/2022 - Mon
Rey Obrero (Capricorn1)

View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Dale Fye

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]

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... :)
Rey Obrero (Capricorn1)

<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.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes