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.SnapShotDat
strSQLtext = strSQLtext & "FROM Quotes LEFT JOIN ComponentSlave ON Quotes.SearchNumber = ComponentSlave.SearchNumbe
strSQLtext = strSQLtext & "where (((ComponentSlave.SnapShot
strSQLtext = strSQLtext & "ORDER BY Quotes.StatusDate DESC ;"
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.SnapShotDat
strSQLtext = strSQLtext & "FROM Quotes LEFT JOIN ComponentSlave ON Quotes.SearchNumber = ComponentSlave.SearchNumbe
strSQLtext = strSQLtext & "where (((ComponentSlave.SnapShot
strSQLtext = strSQLtext & "ORDER BY Quotes.StatusDate DESC ;"
Microsoft Access
Last Comment
Rey Obrero (Capricorn1)
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
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.
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... :)
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.
not really, it could be any other library that is missing and can cause the same error.
Although it should not be necessary, you might also want to modify the reference to [Status] in your WHERE clause to Quotes.[Status]