Nz FUNCTION OK IN ACCESS, NO GO IN ADO

[TNN]

I am using the Nz function in a query that was created in Access. The query works fine when I run it from the Access database window but will not work when I move it to VB6.0 and try to use it via ADO. I get error message "Undefined function 'Nz' in expression". The query stores good in the database, residing there. The statement follows:

SELECT SUM((Nz([QTRTTL],0))+(Nz([QTRTTLSTARTUP],0))-(Nz([QTRTTLDEPOSITS],0))) AS PymtAmt
FROM qryQTRTTL, qryQTRTTLSTARTUP, qryQTRTTLDEPOSITS;

TNN, Tom

TOM

http://WWW.TNPAYROLL@AOL.COM

 

[Quehay]

The Nz function may be in the VBA library (not sure). Try adding reference to this library and see if it works. Jeff Roberts
Analysis, Design, & Implementation
RenaissanceData.com

 

[TNN]

Jeff,
Can't find VBA Library in references. Checked Knowledge support, nothing. Do you have a DLL file name??
TNN, Tom

TOM

http://WWW.TNPAYROLL@AOL.COM

 

[Quehay]

It's listed as "Visual Basic for Applications". Jeff Roberts
Analysis, Design, & Implementation
RenaissanceData.com

 

[RottPaws]

Ran into this a while back and just wrote my own function to do the same thing.

Add this to a standard module in your VB app and enjoy.

Function NoNull(varValue As Variant, varIfNull As Variant)

If IsNull(varValue) Then
NoNull = varIfNull
Else
NoNull = varValue
End If

End Function
_________
Rott Paws

...It's not a bug. It's an undocumented feature!!!

 

[TNN]

Jeff,
There are 7 references to "Visual Basic For Applications". I had already had one selected, No help. Why 7 ??
TNN, Tom

TOM

http://WWW.TNPAYROLL@AOL.COM

 

[TNN]

RottPaws,
I am using the Nz Function within a SQL statement. Don't believe I can use your Function from a VB app, can I ???? How??
TNN, Tom

TOM

http://WWW.TNPAYROLL@AOL.COM

 

[Quehay]

Tom,

Yeah, outside of the Access container the inline support for VBA in queries probably ain't there. So you could open up a recordset and do record-level processing of the field(s), using a function like RottPaws suggested.

[tt]
Function NullToNada (varValue As Variant, Optional varIfNull As Variant) as String
Dim strValue as string

If Len(varIfNull) Then

strValue = varIfNull

Else

strValue = "Nada"

End if

If IsNull(varValue) Then

NullToNada = strValue

Else

NullToNada = varValue

End If

End Function[/tt] Jeff Roberts
Analysis, Design, & Implementation
RenaissanceData.com

 

[TNN]

Found it. The IIF and ISNULL in this SQL stmnt does the job:

SELECT ROUND(PaymtAmt,2) AS PymtAmt
FROM(SELECT SUM(IIf(ISNULL([QTRTTL]),0,[QTRTTL])+IIf(ISNULL([QTRTTLSTARTUP]),0,[QTRTTLSTARTUP])-IIf(ISNULL([QTRTTLDEPOSITS]),0,[QTRTTLDEPOSITS])) AS PaymtAmt
FROM qryQTRTTL, qryQTRTTLSTARTUP, qryQTRTTLDEPOSITS)


Thank you for everyones input.

TNN, Tom

TOM

http://WWW.TNPAYROLL@AOL.COM