Intrinsic Functions Not Working

[John Pollitt]

We are having problems with Access 97 queries after we the
have upgraded OS Win95/Win 2000 machines to XP.

Queries which use the IIF or Left or Len functions
generate the following type of error (and the query will
not run):

RunTime Error 3075

Function isn't available in expressions query session
'IIf(Len(Format[Billing_Date]="ww"))=1,'0',Format
([Billing_Date],"ww" etc

The queries run fine on machines with OS 2000 or 98/95.

Any help would be greatly appreciated as we are upgrading
all our machines to XP.

Many Thanks
JOHN
..

 

[Van T. Dinh]

Most likely, Reference Errors.

See Douglas J. Steele's article:

http://members.rogers.com/douglas.j.steele/AccessReferenceErrors.html

 

[Michel Walsh]

Hi,

You probably have a reference problem. While you see some code, from the
menu: Tools | References... then, in the list, among those that are
checked, at least one should have its description starting with the word:
MISSING. Uncheck it.


Hoping it may help,
Vanderghast, Access MVP

 

[John Pollitt]

Hi,

Many thanks for your reply. However the problem does not
seem to be a missing reference to a type library. The
query is not run from code. It is simply using an inbuilt
Access Function IIF in an access query. If i take the
field out of the query the query runs fine

I have checked the references and there is no IS Missing
library.

JOHN

-----Original Message-----
Hi,

You probably have a reference problem. While you see some code, from the
menu: Tools | References... then, in the list, among those that are
checked, at least one should have its description starting with the word:
MISSING. Uncheck it.


Hoping it may help,
Vanderghast, Access MVP

We are having problems with Access 97 queries after we the
have upgraded OS Win95/Win 2000 machines to XP.

Queries which use the IIF or Left or Len functions
generate the following type of error (and the query will
not run):

RunTime Error 3075

Function isn't available in expressions query session
'IIf(Len(Format[Billing_Date]="ww"))=1,'0',Format
([Billing_Date],"ww" etc

The queries run fine on machines with OS 2000 or 98/95.

Any help would be greatly appreciated as we are upgrading
all our machines to XP.

Many Thanks
JOHN
.

.

 

[Michel Walsh]

Hi,


iif is defined in Jet (and in VBA, note that they are also different
in their implementation) ; Len is only defined in VBA and caused a general
check about references to be launched. At that moment, if ANY reference is
MISSING, even one without any relation with the function we look for, the
error occurs. Have you check the references? it takes just 30 sec. to do...



Vanderghast, Access MVP

Hi,

Many thanks for your reply. However the problem does not
seem to be a missing reference to a type library. The
query is not run from code. It is simply using an inbuilt
Access Function IIF in an access query. If i take the
field out of the query the query runs fine

I have checked the references and there is no IS Missing
library.

JOHN

-----Original Message-----
Hi,

You probably have a reference problem. While you see some code, from the
menu: Tools | References... then, in the list, among those that are
checked, at least one should have its description starting with the word:
MISSING. Uncheck it.


Hoping it may help,
Vanderghast, Access MVP

We are having problems with Access 97 queries after we the
have upgraded OS Win95/Win 2000 machines to XP.

Queries which use the IIF or Left or Len functions
generate the following type of error (and the query will
not run):

RunTime Error 3075

Function isn't available in expressions query session
'IIf(Len(Format[Billing_Date]="ww"))=1,'0',Format
([Billing_Date],"ww" etc

The queries run fine on machines with OS 2000 or 98/95.

Any help would be greatly appreciated as we are upgrading
all our machines to XP.

Many Thanks
JOHN
.

.

 

[Guest]

Yes I have checked the references (In the VBE
Tools..References), there is no MISSING reference

JOHN

-----Original Message-----
Hi,


iif is defined in Jet (and in VBA, note that they are also different
in their implementation) ; Len is only defined in VBA and caused a general
check about references to be launched. At that moment, if ANY reference is
MISSING, even one without any relation with the function we look for, the
error occurs. Have you check the references? it takes just 30 sec. to do...



Vanderghast, Access MVP

Hi,

Many thanks for your reply. However the problem does not
seem to be a missing reference to a type library. The
query is not run from code. It is simply using an inbuilt
Access Function IIF in an access query. If i take the
field out of the query the query runs fine

I have checked the references and there is no IS Missing
library.

JOHN

-----Original Message-----
Hi,

You probably have a reference problem. While you

see
some code, from the

menu: Tools | References... then, in the list, among those that are
checked, at least one should have its description starting with the word:
MISSING. Uncheck it.


Hoping it may help,
Vanderghast, Access MVP

We are having problems with Access 97 queries after

we
the

have upgraded OS Win95/Win 2000 machines to XP.

Queries which use the IIF or Left or Len functions
generate the following type of error (and the query will
not run):

RunTime Error 3075

Function isn't available in expressions query session
'IIf(Len(Format[Billing_Date]="ww"))=1,'0',Format
([Billing_Date],"ww" etc

The queries run fine on machines with OS 2000 or 98/95.

Any help would be greatly appreciated as we are upgrading
all our machines to XP.

Many Thanks
JOHN
.




.

.

 

[Van T. Dinh]

Try unchecking a Reference, OK'ing to get out, getting back in the
References and re-checking the unchecked Reference as per Douglas J.
Steele's article.

--
HTH
Van T. Dinh
MVP (Access)

Yes I have checked the references (In the VBE
Tools..References), there is no MISSING reference

JOHN

-----Original Message-----
Hi,


iif is defined in Jet (and in VBA, note that they are also different
in their implementation) ; Len is only defined in VBA and caused a general
check about references to be launched. At that moment, if ANY reference is
MISSING, even one without any relation with the function we look for, the
error occurs. Have you check the references? it takes just 30 sec. to do...



Vanderghast, Access MVP

Hi,

Many thanks for your reply. However the problem does not
seem to be a missing reference to a type library. The
query is not run from code. It is simply using an inbuilt
Access Function IIF in an access query. If i take the
field out of the query the query runs fine

I have checked the references and there is no IS Missing
library.

JOHN







-----Original Message-----
Hi,

You probably have a reference problem. While you see
some code, from the
menu: Tools | References... then, in the list, among
those that are
checked, at least one should have its description
starting with the word:
MISSING. Uncheck it.


Hoping it may help,
Vanderghast, Access MVP

"John Pollitt" <[email protected]>
wrote in message
We are having problems with Access 97 queries after we
the
have upgraded OS Win95/Win 2000 machines to XP.

Queries which use the IIF or Left or Len functions
generate the following type of error (and the query will
not run):

RunTime Error 3075

Function isn't available in expressions query session
'IIf(Len(Format[Billing_Date]="ww"))=1,'0',Format
([Billing_Date],"ww" etc

The queries run fine on machines with OS 2000 or 98/95.

Any help would be greatly appreciated as we are
upgrading
all our machines to XP.

Many Thanks
JOHN
.




.

.

 

[John Pollitt]

Many thanks.

Although I had no MISSING references I checked the
references that were set. I then set a reference to the
ADO library. The problem has now gone !!!

NB I am worried though that this may cause conflicts code.
In the Access 97 database DAO recordsets are used in code.
With a reference to ADO which component will be used DA0
or ADO. Will any problems result ?

Many thanks to both Michael and Dan.

Regards
JOHN

-----Original Message-----
Try unchecking a Reference, OK'ing to get out, getting back in the
References and re-checking the unchecked Reference as per Douglas J.
Steele's article.

--
HTH
Van T. Dinh
MVP (Access)

Yes I have checked the references (In the VBE
Tools..References), there is no MISSING reference

JOHN

-----Original Message-----
Hi,


iif is defined in Jet (and in VBA, note that they are also different
in their implementation) ; Len is only defined in VBA

and
caused a general

check about references to be launched. At that moment,

if
ANY reference is

MISSING, even one without any relation with the

function
we look for, the

error occurs. Have you check the references? it takes just 30 sec. to do...



Vanderghast, Access MVP



Hi,

Many thanks for your reply. However the problem does not
seem to be a missing reference to a type library. The
query is not run from code. It is simply using an inbuilt
Access Function IIF in an access query. If i take the
field out of the query the query runs fine

I have checked the references and there is no IS Missing
library.

JOHN







-----Original Message-----
Hi,

You probably have a reference problem. While you see
some code, from the
menu: Tools | References... then, in the list, among
those that are
checked, at least one should have its description
starting with the word:
MISSING. Uncheck it.


Hoping it may help,
Vanderghast, Access MVP

"John Pollitt" <[email protected]>
wrote in message
We are having problems with Access 97 queries

after
we

the
have upgraded OS Win95/Win 2000 machines to XP.

Queries which use the IIF or Left or Len functions
generate the following type of error (and the

query
will

not run):

RunTime Error 3075

Function isn't available in expressions query session
'IIf(Len(Format[Billing_Date]="ww"))=1,'0',Format
([Billing_Date],"ww" etc

The queries run fine on machines with OS 2000 or 98/95.

Any help would be greatly appreciated as we are
upgrading
all our machines to XP.

Many Thanks
JOHN
.




.



.

.

 

[Michel Walsh]

Hi,


Sure, there is a great potential of problems. To be safe, make the DAO
reference HIGHER than the ADO one, to be extra safe, remove the possible
ambiguities using the library name:


Dim rst As DAO.Recordset
Dim uvw As ADODB.Recordset


to then uniquely differentiate between the two types of recordsets.


Fortunately, ADO has few classes, so, a limited number of possible
collisions, but if a compile error occur about your object not supporting
one of the method it should, or if an assignment fails at run time without
valid reason, I would start checking about that kind of possible library
mismatch.


Hoping it may help,
Vanderghast, Access MVP

Many thanks.

Although I had no MISSING references I checked the
references that were set. I then set a reference to the
ADO library. The problem has now gone !!!

NB I am worried though that this may cause conflicts code.
In the Access 97 database DAO recordsets are used in code.
With a reference to ADO which component will be used DA0
or ADO. Will any problems result ?

Many thanks to both Michael and Dan.

Regards
JOHN

-----Original Message-----
Try unchecking a Reference, OK'ing to get out, getting back in the
References and re-checking the unchecked Reference as per Douglas J.
Steele's article.

--
HTH
Van T. Dinh
MVP (Access)

Yes I have checked the references (In the VBE
Tools..References), there is no MISSING reference

JOHN


-----Original Message-----
Hi,


iif is defined in Jet (and in VBA, note that they
are also different
in their implementation) ; Len is only defined in VBA and
caused a general
check about references to be launched. At that moment, if
ANY reference is
MISSING, even one without any relation with the function
we look for, the
error occurs. Have you check the references? it takes
just 30 sec. to do...



Vanderghast, Access MVP



"John Pollitt" <[email protected]>
wrote in message
Hi,

Many thanks for your reply. However the problem does not
seem to be a missing reference to a type library. The
query is not run from code. It is simply using an
inbuilt
Access Function IIF in an access query. If i take the
field out of the query the query runs fine

I have checked the references and there is no IS Missing
library.

JOHN







-----Original Message-----
Hi,

You probably have a reference problem. While you
see
some code, from the
menu: Tools | References... then, in the list, among
those that are
checked, at least one should have its description
starting with the word:
MISSING. Uncheck it.


Hoping it may help,
Vanderghast, Access MVP

"John Pollitt" <[email protected]>
wrote in message
We are having problems with Access 97 queries after
we
the
have upgraded OS Win95/Win 2000 machines to XP.

Queries which use the IIF or Left or Len functions
generate the following type of error (and the query
will
not run):

RunTime Error 3075

Function isn't available in expressions query session
'IIf(Len(Format[Billing_Date]="ww"))=1,'0',Format
([Billing_Date],"ww" etc

The queries run fine on machines with OS 2000 or
98/95.

Any help would be greatly appreciated as we are
upgrading
all our machines to XP.

Many Thanks
JOHN
.




.



.

.

 

[John Pollitt]

Thanks for the advice.

I suspected this might cause a problem. Fortunately there
are only a few procedures in the database. I prefer your
suggestion to qualify the library (DAO.Recordset) as the
best remedy, although I will rewrite all the code using
ADO for easier future upgrade.

Again thanks for your help, it is much appreciated.

Regards
JOHN

-----Original Message-----
Hi,


Sure, there is a great potential of problems. To be safe, make the DAO
reference HIGHER than the ADO one, to be extra safe, remove the possible
ambiguities using the library name:


Dim rst As DAO.Recordset
Dim uvw As ADODB.Recordset


to then uniquely differentiate between the two types of recordsets.


Fortunately, ADO has few classes, so, a limited number of possible
collisions, but if a compile error occur about your object not supporting
one of the method it should, or if an assignment fails at run time without
valid reason, I would start checking about that kind of possible library
mismatch.


Hoping it may help,
Vanderghast, Access MVP

Many thanks.

Although I had no MISSING references I checked the
references that were set. I then set a reference to the
ADO library. The problem has now gone !!!

NB I am worried though that this may cause conflicts code.
In the Access 97 database DAO recordsets are used in code.
With a reference to ADO which component will be used DA0
or ADO. Will any problems result ?

Many thanks to both Michael and Dan.

Regards
JOHN

-----Original Message-----
Try unchecking a Reference, OK'ing to get out, getting back in the
References and re-checking the unchecked Reference as

per
Douglas J.

Steele's article.

--
HTH
Van T. Dinh
MVP (Access)



Yes I have checked the references (In the VBE
Tools..References), there is no MISSING reference

JOHN


-----Original Message-----
Hi,


iif is defined in Jet (and in VBA, note that they
are also different
in their implementation) ; Len is only defined in

VBA
and

caused a general
check about references to be launched. At that

moment,
if

ANY reference is
MISSING, even one without any relation with the function
we look for, the
error occurs. Have you check the references? it takes
just 30 sec. to do...



Vanderghast, Access MVP



"John Pollitt" <[email protected]>
wrote in message
Hi,

Many thanks for your reply. However the problem

does
not

seem to be a missing reference to a type library. The
query is not run from code. It is simply using an
inbuilt
Access Function IIF in an access query. If i take the
field out of the query the query runs fine

I have checked the references and there is no IS Missing
library.

JOHN







-----Original Message-----
Hi,

You probably have a reference problem. While you
see
some code, from the
menu: Tools | References... then, in the list, among
those that are
checked, at least one should have its description
starting with the word:
MISSING. Uncheck it.


Hoping it may help,
Vanderghast, Access MVP

"John Pollitt"

wrote in message
We are having problems with Access 97 queries after
we
the
have upgraded OS Win95/Win 2000 machines to XP.

Queries which use the IIF or Left or Len functions
generate the following type of error (and the query
will
not run):

RunTime Error 3075

Function isn't available in expressions query session
'IIf(Len(Format[Billing_Date]="ww")) =1,'0',Format
([Billing_Date],"ww" etc

The queries run fine on machines with OS 2000 or
98/95.

Any help would be greatly appreciated as we are
upgrading
all our machines to XP.

Many Thanks
JOHN
.




.



.



.

.

 

[Van T. Dinh]

If you are still using JET back-end, stick with DAO since DAO is more
efficient than ADO with JET back-end.