Error 3085: Undefined function 'StrReverse' in expression.

[Guest]

Hi,

Could someone please help me?

I created a SQL code in a QUERY that uses the StrReverse function. Inside of
the Access database that query works perfectly.

However, when I call the same SQL code from a VB program using a DAO
recordset, I receive the following error message:

Error 3085: Undefined function 'StrReverse' in expression.

I already updated my system with the JET 4.0 Service Pack 8, but this
problem continues occurring.

Does someone know why?

Thanks in advance for your help,

 

[Douglas J Steele]

When you're running queries from outside of Access, you don't have the
ability to use most VBA functions.

There's no real work-around for this.

 

[Dirk Goldgar]

Hi,

Could someone please help me?

I created a SQL code in a QUERY that uses the StrReverse function.
Inside of the Access database that query works perfectly.

However, when I call the same SQL code from a VB program using a DAO
recordset, I receive the following error message:

Error 3085: Undefined function 'StrReverse' in expression.

I already updated my system with the JET 4.0 Service Pack 8, but this
problem continues occurring.

Does someone know why?

Although most of the built-in VBA functions can be used in queries that
run outside the Microsoft Access environment, I don't think StrReverse
is one of them, See Brendan Reynolds' test results here:

http://brenreyn.blogspot.com/ (Friday, September 17, 2004)

Maybe you can revise the query in such a way that it only uses the
supported functions.

 

[Guest]

Hi Dirk,

thanks for your help.

Could you tell me if those functions should work if I disabled the sandbox
mode in the registry?

 

[Douglas J Steele]

That won't make a difference.

 

[Guest]

Hi Douglas,

Thanks for your help.

Yes, you are right. I'll have to change my logic to use only the allowed
functions. However, I saw in the http://support.microsoft.com/kb/294698/ link
that all of the functions should work if I disable the sandbox mode.

This didn't work in my PC. I changed the registry so that the sandbox mode
was set to zero and I continue receiving the same error.

Thanks,

 

[Dirk Goldgar]

When you're running queries from outside of Access, you don't have the
ability to use most VBA functions.

I think Brendan demonstrated that most of them *will* work (provided
that VBA is installed on the server). But there are definitely some
that won't.

 

[Guest]

Yes, I realized.

Thanks for your help. I'll change my logic.

 

[Tony Toews]

I created a SQL code in a QUERY that uses the StrReverse function. Inside of
the Access database that query works perfectly.

Presumably you are using Access to store the data thus this idea won't
work at all.

If your data was being stored in SQL Server 2005 you can now use
VB.Net inside the store procedures. (I have no idea as to the details
as I've never done that.)

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[Guest]

Hi Tony,

I can't change the database from Access to SQL server due to design issues
for the system that I support.
I changed my logic so that the data is changed before it is stored in the
Access database, so when the program retrieves its data, it is already
changed like I wanted.

Thanks for your help,

 

[Tony Toews]

I can't change the database from Access to SQL server due to design issues
for the system that I support.
I changed my logic so that the data is changed before it is stored in the
Access database, so when the program retrieves its data, it is already
changed like I wanted.

Good solution so long as there are only a few places that every update
the data that is used to calculate this field. This is the kind of
solution that needs to be documented somewhere where it is easily
found by other developers should you move on.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm