access and asp

[sweetleaf]

Hi,

I have an asp page that retrieves records from an access system and displays results in an html table.
One of the columns which i am querying is an expression which adds the values of other columns in the query grid like this:

a: nz([field1]) + nz([field2])

However, asp gives me the following error:

Microsoft JET Database Engine error '80040e14'
Undefined function 'nz' in expression.

Does anyone know a way around this?

Any and all help is greatly appreciated!

 

[UKRacer]

Hi sweetleaf -

Firstly, I think that if you use the nz function in a query, you MUST provide an "if null" argument; hence if you want your values to be zero, your expression should read:

a: nz([field1],0) + nz([field2],0)

BUT I have a feeling that the nz function would not be recognised in asp script (try it and see!)

If not, you can use the following in the query:

a: IIf(IsNull([field1]),0,[field1]) + IIf(IsNull([field2]),0,[field2])

replacing the zero with whatever you want in place of the null value.

Hope this helps,

Pete