Access 97 Left() function rejected

[JimS]

I have a query that works just fine on my computer where I have A97 installed
along with A2007. They don't like each other, and they don't play well, but
my client needs A97, so I use it. The query fails when I transfer the .mdb
over to their network and run it. Approximate message is: "Function not
available in query...."

Here's the query straight from the SQL view of the query:

SELECT CStr([contract #]) AS ContractNbr, Left(CStr([contract #]),8) AS
Job_Nbr, Right(CStr([contract #]),3) AS VendorNbr, IIf([sub/po?]="PO",2,1) AS
Contract_Type, IIf([Per T & C of another contract?]="Y",False,True) AS
Contract_Referencable, [SubPO log].*
FROM [SubPO log]
WHERE (((Len([contract #]))=12));

The function it rejects is "Left(CStr([contract #]),8) AS Job_Nbr". I
removed the CStr() part of the phrase {CStr is redundant since it's already a
string...}, and got the same result. I changed left() to Left$(). No Joy. I
have to believe it has something with my client's use of the "#" sign, but
this works fine on my machine, and fails on theirs...


My machine is running A97 SR-2, their machines run SR-1. Anybody know how to
get SR-2's update?

 

[Ofer Cohen]

When bulid in function of Access are not recognized, it's usually happen when
there is a broken reference.

To check if that the case, open code view (any where), from the TooBar select
Tools > Reference

Check if the word missig apears to next to one of the references selected.
You can uncheck it if there is no need for that reference, or select the
right path to it.

 

[JimS]

Good catch...I am missing: Microsoft Activex Data Objects 2.8 Library.
Yet, the file exists exactly where it is shown to exist in the reference.
The file name is shown as msado15.dll. msado15.dll exists and reports as
version 2.53.6200.0....It's apparently the same file that is referenced on my
machine...in the same place...and not shown as missing.

I'm confused....

--
Jim

When bulid in function of Access are not recognized, it's usually happen when
there is a broken reference.

To check if that the case, open code view (any where), from the TooBar select
Tools > Reference

Check if the word missig apears to next to one of the references selected.
You can uncheck it if there is no need for that reference, or select the
right path to it.

--
Good Luck
BS"D

I have a query that works just fine on my computer where I have A97 installed
along with A2007. They don't like each other, and they don't play well, but
my client needs A97, so I use it. The query fails when I transfer the .mdb
over to their network and run it. Approximate message is: "Function not
available in query...."

Here's the query straight from the SQL view of the query:

SELECT CStr([contract #]) AS ContractNbr, Left(CStr([contract #]),8) AS
Job_Nbr, Right(CStr([contract #]),3) AS VendorNbr, IIf([sub/po?]="PO",2,1) AS
Contract_Type, IIf([Per T & C of another contract?]="Y",False,True) AS
Contract_Referencable, [SubPO log].*
FROM [SubPO log]
WHERE (((Len([contract #]))=12));

The function it rejects is "Left(CStr([contract #]),8) AS Job_Nbr". I
removed the CStr() part of the phrase {CStr is redundant since it's already a
string...}, and got the same result. I changed left() to Left$(). No Joy. I
have to believe it has something with my client's use of the "#" sign, but
this works fine on my machine, and fails on theirs...


My machine is running A97 SR-2, their machines run SR-1. Anybody know how to
get SR-2's update?

 

[Ofer Cohen]

UnSelect the Activex, and reselect the msado15 on the mechine that you get
the problem, what version of Library do you get? Is it the same one?

--
Good Luck
BS"D

Good catch...I am missing: Microsoft Activex Data Objects 2.8 Library.
Yet, the file exists exactly where it is shown to exist in the reference.
The file name is shown as msado15.dll. msado15.dll exists and reports as
version 2.53.6200.0....It's apparently the same file that is referenced on my
machine...in the same place...and not shown as missing.

I'm confused....

--
Jim

When bulid in function of Access are not recognized, it's usually happen when
there is a broken reference.

To check if that the case, open code view (any where), from the TooBar select
Tools > Reference

Check if the word missig apears to next to one of the references selected.
You can uncheck it if there is no need for that reference, or select the
right path to it.

--
Good Luck
BS"D

I have a query that works just fine on my computer where I have A97 installed
along with A2007. They don't like each other, and they don't play well, but
my client needs A97, so I use it. The query fails when I transfer the .mdb
over to their network and run it. Approximate message is: "Function not
available in query...."

Here's the query straight from the SQL view of the query:

SELECT CStr([contract #]) AS ContractNbr, Left(CStr([contract #]),8) AS
Job_Nbr, Right(CStr([contract #]),3) AS VendorNbr, IIf([sub/po?]="PO",2,1) AS
Contract_Type, IIf([Per T & C of another contract?]="Y",False,True) AS
Contract_Referencable, [SubPO log].*
FROM [SubPO log]
WHERE (((Len([contract #]))=12));

The function it rejects is "Left(CStr([contract #]),8) AS Job_Nbr". I
removed the CStr() part of the phrase {CStr is redundant since it's already a
string...}, and got the same result. I changed left() to Left$(). No Joy. I
have to believe it has something with my client's use of the "#" sign, but
this works fine on my machine, and fails on theirs...


My machine is running A97 SR-2, their machines run SR-1. Anybody know how to
get SR-2's update?

 

[JimS]

OK, I did that. It picked up an earlier version of the package... but now it
goes pretty much through the entire 10-query process (it had been failing on
step 8....) and creates a windows memory fault, crashing A97. No code here,
just clears a dozen tables, recreates them from other tables (all tables are
linked...), then runs a final integrity check query on the resulting set of
tables. It barfs on the last step.
--
Jim

UnSelect the Activex, and reselect the msado15 on the mechine that you get
the problem, what version of Library do you get? Is it the same one?

--
Good Luck
BS"D

Good catch...I am missing: Microsoft Activex Data Objects 2.8 Library.
Yet, the file exists exactly where it is shown to exist in the reference.
The file name is shown as msado15.dll. msado15.dll exists and reports as
version 2.53.6200.0....It's apparently the same file that is referenced on my
machine...in the same place...and not shown as missing.

I'm confused....

--
Jim

When bulid in function of Access are not recognized, it's usually happen when
there is a broken reference.

To check if that the case, open code view (any where), from the TooBar select
Tools > Reference

Check if the word missig apears to next to one of the references selected.
You can uncheck it if there is no need for that reference, or select the
right path to it.

--
Good Luck
BS"D


:

I have a query that works just fine on my computer where I have A97 installed
along with A2007. They don't like each other, and they don't play well, but
my client needs A97, so I use it. The query fails when I transfer the .mdb
over to their network and run it. Approximate message is: "Function not
available in query...."

Here's the query straight from the SQL view of the query:

SELECT CStr([contract #]) AS ContractNbr, Left(CStr([contract #]),8) AS
Job_Nbr, Right(CStr([contract #]),3) AS VendorNbr, IIf([sub/po?]="PO",2,1) AS
Contract_Type, IIf([Per T & C of another contract?]="Y",False,True) AS
Contract_Referencable, [SubPO log].*
FROM [SubPO log]
WHERE (((Len([contract #]))=12));

The function it rejects is "Left(CStr([contract #]),8) AS Job_Nbr". I
removed the CStr() part of the phrase {CStr is redundant since it's already a
string...}, and got the same result. I changed left() to Left$(). No Joy. I
have to believe it has something with my client's use of the "#" sign, but
this works fine on my machine, and fails on theirs...


My machine is running A97 SR-2, their machines run SR-1. Anybody know how to
get SR-2's update?

 

[John W. Vinson]

Good catch...I am missing: Microsoft Activex Data Objects 2.8 Library.
Yet, the file exists exactly where it is shown to exist in the reference.
The file name is shown as msado15.dll. msado15.dll exists and reports as
version 2.53.6200.0....It's apparently the same file that is referenced on my
machine...in the same place...and not shown as missing.

This can happen when Access just gets confused (often due to installing some
OTHER software). Uncheck the MISSING reference; close and open Access; check
it again, and try Debug... Compile. If that succeeds close the VBA editor and
select Tools... Database utilites... Compact and Repair and you should be OK.

John W. Vinson [MVP]