NZ Function through Microsoft Jet

[xangelusx]

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

 

[Pat Hartman]

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

 

[xangelusx]

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).]

 

[Pat Hartman]

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