Hi Folks:

A user named Harry kindly turned me on to the Nz() function in Access. I've been using it liberaly but only recently did I try to use it from an ASP page via Microsoft Jet. I'm using an DSN-Less connection and I'm now getting this error:
----------
Microsoft JET Database Engine error '80040e14'

Undefined function 'Nz' in expression.

/bbx/forums.asp, line 48
----------

Line 48 reffers to my objRecordset.Open line. I pass it a SQL query that uses the NZ() function in a where clause.

Any suggestions on overcoming this?

Regards,
Chris Bloom

Does the query work when you run it from within the database? Post the entire SQL string for better help.

Pat,

Yes, the query works wonderful from within Access. But once I try to run it from ASP I would get the 'Undefined Function' error. I found a work around, but I'd still like to know why I am unable to use the function through Microsoft JET.

Here is the original query:
----------SELECT f1.forum_id,
f1.forum_name,
f1.forum_desc,
Sum(IIf([t1].[thread_disabled]=0,1,0)) AS forum_post_count,
Last(t1.thread_id) AS forum_last_post,
Last(t1.thread_timestamp) AS forum_last_timestamp,
Last(t1.thread_author_link) AS forum_last_author_id,
Last(u1.user_handle) AS forum_last_author_handle

FROM (bbx_forums AS f1 LEFT JOIN bbx_threads AS t1 ON f1.forum_id = t1.thread_forum_link) LEFT JOIN bbx_users AS u1 ON t1.thread_author_link = u1.user_id

WHERE f1.forum_disabled = 0
AND nz(t1.thread_parent_link,0) = 0
AND nz(t1.thread_disabled,0) = 0

GROUP BY f1.forum_id,
f1.forum_name,
f1.forum_desc

ORDER BY f1.forum_id;----------

Regards,
Chris Bloom


[This message has been edited by xangelusx (edited 03-21-2002).]

I don't know why Nz() won't work but you could test for null directly instead. You don't need the Nz() function here:

AND t1.thread_parent_link Is Null
AND t1.thread_disabled Is Null