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

Discussion in 'ASP General' started by aa, Jan 25, 2004.

  1. aa

    aa Guest

    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?
    aa, Jan 25, 2004
    1. Advertisements

  2. aa

    Bob Barrows Guest

    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):;en-us;294698&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.

    Bob Barrows
    Bob Barrows, Jan 25, 2004
    1. Advertisements

  3. Aaron Bertrand [MVP], Jan 25, 2004
  4. 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:

    Option Compare Database

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

    3. Created the following query:

    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
    Chris Hohmann, Jan 26, 2004
  5. aa

    Bob Barrows Guest


    Bob Barrows
    Bob Barrows, Jan 26, 2004
  6. aa

    Guest Guest

    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?
    Guest, Jan 26, 2004
  7. thick and I failed to find any mentioning of Nz or IIF there
    No, they are bastardized VBA/SQL hybrid.
    Access has online help...
    Aaron Bertrand - MVP, Jan 26, 2004
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.