Undefined function 'NZ' in expression.

[dhborchardt]

I have a connection to an Access database. I click the select button and the query I want is not listed. I can get the query to show by removing a part of it. If I add that portion back in I will get the error "ErrorSource: Microsoft JET Database Engine, ErrorMsg: Undefined function 'NZ' in expression."

Does QlikView not like the NZ function? I have the SQL below with the the section that causes the problem in BOLD text. Maybe I should do this in the load scrip instead?

SELECT dbo_AbsDrgDiagnoses.DiagnosisSeqID, dbo_AbsDrgDiagnoses.Diagnosis, dbo_AbsDrgDiagnoses.DiagnosisName, dbo_AbsDrgDiagnoses.RowUpdateDateTime, dbo_AbsDrgDiagnoses.AbstractID, NZ([PresentAtAdmit],'N') AS AbsDiagCodes
FROM dbo_AbsDrgDiagnoses
WHERE (((dbo_AbsDrgDiagnoses.RowUpdateDateTime)>#9/1/2009#));

[Miguel_Angel_Baeyens · Accepted solution]

Hello,

Note that it's not related to QlikView rather to the driver you are using, since QlikView doesn't parse what you SELECT, so I'd recommend to do a LOAD preceding the SQL SELECT:

Table:LOAD dbo_AbsDrgDiagnoses.DiagnosisSeqID, dbo_AbsDrgDiagnoses.Diagnosis, dbo_AbsDrgDiagnoses.DiagnosisName, dbo_AbsDrgDiagnoses.RowUpdateDateTime, dbo_AbsDrgDiagnoses.AbstractID, If(Len([PresentAtAdmit]) = 0, 'N', [PresentAtAdmit]) AS AbsDiagCodes; // If the value is empty/null return "N", otherwise, the valueSQL SELECT dbo_AbsDrgDiagnoses.DiagnosisSeqID, dbo_AbsDrgDiagnoses.Diagnosis, dbo_AbsDrgDiagnoses.DiagnosisName, dbo_AbsDrgDiagnoses.RowUpdateDateTime, dbo_AbsDrgDiagnoses.AbstractID, [PresentAtAdmit]FROM dbo_AbsDrgDiagnosesWHERE (((dbo_AbsDrgDiagnoses.RowUpdateDateTime)>#9/1/2009#));

Hope that helps.

[dhborchardt]

Thanks, that worked