Function is not available in expressions in query expression

[M Skabialka]

I made some changes to a database and put it out for users but they are
getting errors:
Function is not available in expressions in query expression
'IIf(Left({DocName],1)="L",Mid([DocName],3,20),[DocName])]'

The fix I found on the Internet was to go into Tools References in the code
module, add a reference, close the databse, go back in and remove it. Then
everything works fine.

But I don't want to have to do this on every machine. Is there some code I
can add somewhere that solves this problem so that I don't have to let users
see me getting into the code window on their machine?

 

[Ray C]

Firstly, I noticed an error in your expression. You have a curly brace on the
left side of your DocName variable which should be a square bracket. Could
that be causing the error?

If that's not causing your error, then I suggest you make your reference
correction on one file and simply copy the file on all the machines rather
than modifying each one.

Keep in mind that once a reference is set, you do not need to reset it each
time.

Ray

 

[M Skabialka]

I was using a screen shot to get the info for the message here - the code
doesn't have any errors - that's just an error here in transcription.

I made the references correction in one file, copied it to the server for
everyone else to get a copy. They are still getting the error unless I do
the fix on each machine.

So when a new person gets the database I first have to give them permissions
to the back end, change the macro security level, now do this reference
tweak, and actually I don't do it as it's a remote site and someone else has
to follow my directions - a non-Access person. There needs to be a better
way to deploy Access databases without all the fuss!

Firstly, I noticed an error in your expression. You have a curly brace on
the
left side of your DocName variable which should be a square bracket. Could
that be causing the error?

If that's not causing your error, then I suggest you make your reference
correction on one file and simply copy the file on all the machines rather
than modifying each one.

Keep in mind that once a reference is set, you do not need to reset it
each
time.

Ray

I made some changes to a database and put it out for users but they are
getting errors:
Function is not available in expressions in query expression
'IIf(Left({DocName],1)="L",Mid([DocName],3,20),[DocName])]'

The fix I found on the Internet was to go into Tools References in the
code
module, add a reference, close the databse, go back in and remove it.
Then
everything works fine.

But I don't want to have to do this on every machine. Is there some code
I
can add somewhere that solves this problem so that I don't have to let
users
see me getting into the code window on their machine?

 

[Ray C]

Is it possible that the referenced file is not in the same location (path) on
all the machines? When you copy the database to the server, do you get a
"MISSING" reference when you open the References dialog? If yes, then that is
the problem. Try copying the referenced file in the same path on all machines.

Ray

I was using a screen shot to get the info for the message here - the code
doesn't have any errors - that's just an error here in transcription.

I made the references correction in one file, copied it to the server for
everyone else to get a copy. They are still getting the error unless I do
the fix on each machine.

So when a new person gets the database I first have to give them permissions
to the back end, change the macro security level, now do this reference
tweak, and actually I don't do it as it's a remote site and someone else has
to follow my directions - a non-Access person. There needs to be a better
way to deploy Access databases without all the fuss!

Firstly, I noticed an error in your expression. You have a curly brace on
the
left side of your DocName variable which should be a square bracket. Could
that be causing the error?

If that's not causing your error, then I suggest you make your reference
correction on one file and simply copy the file on all the machines rather
than modifying each one.

Keep in mind that once a reference is set, you do not need to reset it
each
time.

Ray

I made some changes to a database and put it out for users but they are
getting errors:
Function is not available in expressions in query expression
'IIf(Left({DocName],1)="L",Mid([DocName],3,20),[DocName])]'

The fix I found on the Internet was to go into Tools References in the
code
module, add a reference, close the databse, go back in and remove it.
Then
everything works fine.

But I don't want to have to do this on every machine. Is there some code
I
can add somewhere that solves this problem so that I don't have to let
users
see me getting into the code window on their machine?

 

[M Skabialka]

Nothing shows as missing.
The fix is to add any reference, close the database, then reopen and remove
the reference. Problem goes away.
But I'd rather not have a problem I have to fix like this - it makes the
users very antsy!

Is there a permanent fix?

Is it possible that the referenced file is not in the same location (path)
on
all the machines? When you copy the database to the server, do you get a
"MISSING" reference when you open the References dialog? If yes, then that
is
the problem. Try copying the referenced file in the same path on all
machines.

Ray

I was using a screen shot to get the info for the message here - the code
doesn't have any errors - that's just an error here in transcription.

I made the references correction in one file, copied it to the server for
everyone else to get a copy. They are still getting the error unless I
do
the fix on each machine.

So when a new person gets the database I first have to give them
permissions
to the back end, change the macro security level, now do this reference
tweak, and actually I don't do it as it's a remote site and someone else
has
to follow my directions - a non-Access person. There needs to be a
better
way to deploy Access databases without all the fuss!

Firstly, I noticed an error in your expression. You have a curly brace
on
the
left side of your DocName variable which should be a square bracket.
Could
that be causing the error?

If that's not causing your error, then I suggest you make your
reference
correction on one file and simply copy the file on all the machines
rather
than modifying each one.

Keep in mind that once a reference is set, you do not need to reset it
each
time.

Ray


:

I made some changes to a database and put it out for users but they
are
getting errors:
Function is not available in expressions in query expression
'IIf(Left({DocName],1)="L",Mid([DocName],3,20),[DocName])]'

The fix I found on the Internet was to go into Tools References in the
code
module, add a reference, close the databse, go back in and remove it.
Then
everything works fine.

But I don't want to have to do this on every machine. Is there some
code
I
can add somewhere that solves this problem so that I don't have to let
users
see me getting into the code window on their machine?

 

[Ray C]

You said that you fond the fix on the internet somewhere, can you tell me the
address where you found the fix? Maybe it's a known bug.

 

[M Skabialka]

I have tried to find it again - I was on another machine in another office
at the time and haven't been back there to search through history for it. I
haven't been able to find it again, though I swear it was on Microsoft's
site, but don't know where.

 

[AnotherNewGuy]

I have the same problem with an old MDE file that is no longer supported. I
can't get into the code module. Can anyone suggest a way to fix this one?

The computer they ran it on is XP Pro with Access 2003, and the new one we
replaced it with is also XP Pro with Access 2003. Why it works on one and
not the other is beyond me.