Undefined Function "Nz" in expression???

[Darryn]

Hi

I keep getting this message when i run my query from code?? but it runs fine
from Access! what is the problem and how do i resolve it?

Thanks

Darryn

 

[Wolfgang Kais]

I keep getting this message when i run my query from code?? but it runs
fine from Access! what is the problem and how do i resolve it?

Hello Darryn.
This can happen when broken links exist.
Open a module and check "tools, references".

 

[Brendan Reynolds]

When you say 'code' do you mean VBA code within an Access application, or
are you trying to use this query in some other environment, e.g. VB6 or
VBScript? If it's the latter, you can't do that. The NZ() function is a
member of the Access object library and is not available when executing
queries outside of the Access environment. You'll need to replace it with an
IIf() expression instead.

 

[Van T. Dinh]

Nz() is an Access function. When you run a Query in Access, the inbuilt
Expression Service passes the Nz() function to Access VBA for evaluation.
Otherwise, the JET engine doesn't know anything about Nz().

When you run the Query from outside Access, you are only using JET without
the Expression Service to handle Nz() and JET will return error the function
Nz() is undefined.

 

[Darryn]

By code i mean my .Net environment, I am programming in C#

I Have made the changes you suggested and replaced all Nz() with IIf() and
it did not work as the Nz() funtion does. My query will now execute from
code but i am still getting Nulls returned instead of 0 when the result is
0.

Here is my query...

SELECT IIf([TotalDebit]=Null,0,[TotalDebit]) AS TotalDebitAmt,
IIf([TotalCredit]=Null,0,[TotalCredit]) AS TotalCreditAmt,
IIf([Reconciled]=Null,0,[Reconciled]) AS ReconciledAmt,
IIf([UnReconciled]=Null,0,[UnReconciled]) AS UnReconciledAmt,
IIf([TotalOutstanding]=Null,0,[TotalOutstanding]) AS TotalOutstandingAmt,
([TotalDebitAmt]-[TotalCreditAmt])-[TotalOutstandingAmt] AS BankStatement
FROM qryCBReconciledTotal, qryCBUnReconciledTotal, qryCBOutstandingTotal;

if i replace the IIf() with Nz() i get 0 when the Totals are 0 with the
IIf() they still return Null???

 

[John Spencer (MVP)]

Pardon me for jumping in.

IIF(TotalDebit IS NULL, 0, ...)

Null values are unknown and cannot be compared to any other values. You must
use the is Null and Is Not Null operators for the comparison.

By code i mean my .Net environment, I am programming in C#

I Have made the changes you suggested and replaced all Nz() with IIf() and
it did not work as the Nz() funtion does. My query will now execute from
code but i am still getting Nulls returned instead of 0 when the result is
0.

Here is my query...

SELECT IIf([TotalDebit]=Null,0,[TotalDebit]) AS TotalDebitAmt,
IIf([TotalCredit]=Null,0,[TotalCredit]) AS TotalCreditAmt,
IIf([Reconciled]=Null,0,[Reconciled]) AS ReconciledAmt,
IIf([UnReconciled]=Null,0,[UnReconciled]) AS UnReconciledAmt,
IIf([TotalOutstanding]=Null,0,[TotalOutstanding]) AS TotalOutstandingAmt,
([TotalDebitAmt]-[TotalCreditAmt])-[TotalOutstandingAmt] AS BankStatement
FROM qryCBReconciledTotal, qryCBUnReconciledTotal, qryCBOutstandingTotal;

if i replace the IIf() with Nz() i get 0 when the Totals are 0 with the
IIf() they still return Null???

When you say 'code' do you mean VBA code within an Access application, or
are you trying to use this query in some other environment, e.g. VB6 or
VBScript? If it's the latter, you can't do that. The NZ() function is a
member of the Access object library and is not available when executing
queries outside of the Access environment. You'll need to replace it with an
IIf() expression instead.