NZ function?

[js]

Hello,

I can use a NZ function query in a VBA code, but got error when I using ADO
Dim cnn As New ADODB.Connection
Dim rs As New ADODB.Recordset
cnn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=..."
cnn.Open
rs.Open "select NZ(field1,0) from tb1", cnn
rs.Close
cnn.Close

Error: Underfined function 'NZ' in expression?

How to fix it? Thanks..

 

[MyndPhlyp]

Hello,

I can use a NZ function query in a VBA code, but got error when I using ADO
Dim cnn As New ADODB.Connection
Dim rs As New ADODB.Recordset
cnn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=..."
cnn.Open
rs.Open "select NZ(field1,0) from tb1", cnn
rs.Close
cnn.Close

Error: Underfined function 'NZ' in expression?

How to fix it? Thanks..

How about:

SELECT IsNull(field1, 0) FROM tbl

?

 

[js]

Thanks for the help.
Then run-time error: -2147217900(80040e14)
Wrong number of arguments used with function in query expression (xxx).

 

[Brian Kastel]

Should be: SELECT IIf(IsNull(field1), 0, field1) FROM tbl

Syntax: IIf(testvar, truepart, falsepart)

--
Brian Kastel


--Original Message----------------

Thanks for the help.
Then run-time error: -2147217900(80040e14)
Wrong number of arguments used with function in query expression (xxx).

 

[js]

Thanks Brian,
Why I can't use NZ in a query?

Should be: SELECT IIf(IsNull(field1), 0, field1) FROM tbl

Syntax: IIf(testvar, truepart, falsepart)

--
Brian Kastel


--Original Message----------------

Thanks for the help.
Then run-time error: -2147217900(80040e14)
Wrong number of arguments used with function in query expression (xxx).

 

[Brian Kastel]

That's a separate issue that I don't know the answer to since I don't use
ADO (not from an particular position, but just because I've never had a
reason to). If that worked, then it's just a workaround for you until you
can get the core issue resolved.

--
Brian Kastel


--Original Message----------------

Thanks Brian,
Why I can't use NZ in a query?

 

[Douglas J. Steele]

Nz is a VBA function. ADO doesn't know anything about VBA and its functions.