473,239 Members | 1,761 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,239 software developers and data experts.

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 9262
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Sérgio Almeida | last post by:
Greetings I'm having problems adding records to an access database. What I want to do is very simple. Here is my code. __CODE_START__ dim sqlStatement sqlStatement="insert into...
2
by: Pink Panther | last post by:
Using the following SQL can the results be explained? Using A97 (with the SP2 for Jet 3.5) or A2002 CREATE TABLE Test (PK Number CONSTRAINT PK_TEST PRIMARY KEY, ParentID Number, Child...
4
by: MX1 | last post by:
Help. I have a complex Access database that I've tied into using ASP. Things work great, except I can't seem to have queries that originate in ASP use the NZ function. Need it in case there are...
42
by: lylefair | last post by:
The file is now available as http://www.ffdba.com/downloads/testingNZ3.dat (rename .dat to .mdb) or http://www.ffdba.com/downloads/testingNZ3.mdb (At time of posting I have not opened the file.)
4
by: Paul | last post by:
Anyone have code that emulates the Nz function in Microsoft Access? In Access it is: Nz(Value as variant, Optional ValueIfNull as Variant) as Variant
2
by: lnd | last post by:
This was probably asked n-times but still: Is there any way to end (commit/rollback) a transaction inside a stored function? (The reason why it is good to commit/rollback in a stored procedure...
1
by: shamlafazal | last post by:
When i use Send Form Results to a Database and an E-mail Address and Use the File Upload Component Simultaneously , am getting an error mentioned below. Please any can help me on this. "Database...
2
blyxx86
by: blyxx86 | last post by:
Hey everyone, I've finally overcome a plethora of development hurdles!! I think my database is ready for usage. Now the only problem I am facing is getting some of my queries to display the proper...
3
by: Alain Bourgeois | last post by:
Dear all, I have an asp module connecting to a MS-access database. I have a vb function MKDate(date, time) in a vba module of this database. I would like to query : SELECT MKDATE(col1,...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.