469,349 Members | 1,528 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,349 developers. It's quick & easy.

Nz() function and JET Database Engine (0x80040E14)

aa
I have beem recommended to use the Nz() function in the ORDER BY part of an
Access2000 stored query.

The query runs correcly from within Access2000
Yet when ran from ASP, it causes an error:

Microsoft JET Database Engine (0x80040E14)
Undefined function 'Nz' in expression.

Where should I define this function?
Jul 19 '05 #1
7 8968
aa wrote:
I have beem recommended to use the Nz() function in the ORDER BY part
of an Access2000 stored query.

The query runs correcly from within Access2000
Yet when ran from ASP, it causes an error:

Microsoft JET Database Engine (0x80040E14)
Undefined function 'Nz' in expression.

Where should I define this function?


There are quite a few Access/VBA functions that can only be used when Access
itself is running. Nz() is one of them. See here for the list of functions
that can be used from external applications (VB, ASP, etc):
http://support.microsoft.com/default...98&Product=acc

Note that the article shows you how to allow the functions to be used, but
this is not recommended from a security standpoint.

Instead of Nz, you can use IIF:

iif(isnull([fieldname], <default value>, [fieldname])

I just noticed an update to this article: Jet SP7 will allow user-defined
functions to be used! If I was still using Access in my applications, I
would be elated.

HTH,
Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
Jul 19 '05 #2
http://www.aspfaq.com/2394
(Someone remind me later to update that article with the information that
Bob posted...)

--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/


"aa" <aa@virgin.net> wrote in message
news:eH**************@TK2MSFTNGP12.phx.gbl...
I have beem recommended to use the Nz() function in the ORDER BY part of an Access2000 stored query.

The query runs correcly from within Access2000
Yet when ran from ASP, it causes an error:

Microsoft JET Database Engine (0x80040E14)
Undefined function 'Nz' in expression.

Where should I define this function?

Jul 19 '05 #3
"Bob Barrows" <re******@NOyahoo.SPAMcom> wrote in message
news:uC**************@TK2MSFTNGP11.phx.gbl...
aa wrote:
I have beem recommended to use the Nz() function in the ORDER BY part of an Access2000 stored query.

The query runs correcly from within Access2000
Yet when ran from ASP, it causes an error:

Microsoft JET Database Engine (0x80040E14)
Undefined function 'Nz' in expression.

Where should I define this function?
There are quite a few Access/VBA functions that can only be used when

Access itself is running. Nz() is one of them. See here for the list of functions that can be used from external applications (VB, ASP, etc):
http://support.microsoft.com/default...98&Product=acc
Note that the article shows you how to allow the functions to be used, but this is not recommended from a security standpoint.

Instead of Nz, you can use IIF:

iif(isnull([fieldname], <default value>, [fieldname])

I just noticed an update to this article: Jet SP7 will allow user-defined functions to be used! If I was still using Access in my applications, I would be elated.


It sounded too good to be true, so I had to go see for myself. Here's
what I did:

1. Installed Jet 4.0 SP8 (4.0.8015.0)

2. Created the following module:

[modHelloWorld]
Option Compare Database

Public Function HelloWorld()
HelloWorld = "Hello World!"
End Function

3. Created the following query:

[qryHelloWorld]
SELECT HelloWorld() AS retVal;

4. Ran the following ASP code:
<%
Dim cn,rs
Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
cn.Open "<Your DSNLess OLEDB Connection String Here>"
cn.qryHelloWorld rs
Response.Write rs(0)
rs.Close : Set rs = Nothing
cn.Close : Set cn = Nothing
%>

No joy. I still get:
Microsoft JET Database Engine (0x80040E14)
Undefined function 'HelloWorld' in expression.

Note, I also changed my registry settings to completely disable
sandboxing per the referenced article. Same results.

Here's my environment:
Windows 2000 Professional (sp3)
Internet Information Server (5.0)
Active Server Pages (3.0)
VBScript (5.6)
MS Jet (04.00.0000)
Microsoft Data Access Components (2.80)
Microsoft OLE DB Provider for Jet (04.00.8015)
OLE DB (02.10)

Has anyone else had success using UDFs with Jet 4.0 sp7 or above?

-Chris Hohmann
Jul 19 '05 #4
Chris Hohmann wrote:
"Bob Barrows" <re******@NOyahoo.SPAMcom> wrote in message
http://support.microsoft.com/default...98&Product=acc

Note that the article shows you how to allow the functions to be
used, but this is not recommended from a security standpoint.

Instead of Nz, you can use IIF:

iif(isnull([fieldname], <default value>, [fieldname])

I just noticed an update to this article: Jet SP7 will allow
user-defined functions to be used! If I was still using Access in my
applications, I
would be elated.


It sounded too good to be true, so I had to go see for myself. Here's
what I did:

1. Installed Jet 4.0 SP8 (4.0.8015.0)


No joy. I still get:
Microsoft JET Database Engine (0x80040E14)
Undefined function 'HelloWorld' in expression.

Darn!

Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
Jul 19 '05 #5
Thank you everybody.

Both Nz() and IIF came as a complete news for me.
I use "Mastering SQL" my Martin Gruber as a reference. It is 1.5 inches
thick and I failed to find any mentioning of Nz or IIF there
Are they part of standard SQL?
Could anybody provide a link I can read about them?

"aa" <aa@virgin.net> wrote in message
news:eH**************@TK2MSFTNGP12.phx.gbl...
I have beem recommended to use the Nz() function in the ORDER BY part of an Access2000 stored query.

The query runs correcly from within Access2000
Yet when ran from ASP, it causes an error:

Microsoft JET Database Engine (0x80040E14)
Undefined function 'Nz' in expression.

Where should I define this function?

Jul 19 '05 #6
> thick and I failed to find any mentioning of Nz or IIF there
Are they part of standard SQL?
No, they are bastardized VBA/SQL hybrid.
Could anybody provide a link I can read about them?


Access has online help...

--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
Jul 19 '05 #7
Thanks.
It did work for me
Jul 19 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Sérgio Almeida | last post: by
42 posts views Thread by lylefair | last post: by
4 posts views Thread by Paul | last post: by
3 posts views Thread by Alain Bourgeois | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.