Re: Nz Problem in ADO execute


What is odd about all this is that in my tests the field 'Freight' did not
contain any Null values. So I don't think data type conversion fully
explains what's going on here. "33.5" + "25" = "25" is every bit as
incorrect as 33.5 + 25 = 25, right?

--
Brendan Reynolds
Access MVP

"Brendan Reynolds" <brenreyn@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:uXH0zGQTGHA.5924@xxxxxxxxxxxxxxxxxxxxxxx
The problem certainly seems to be related to implicit type conversion in
some way. Interestingly enough, though, it does not occur when the same
query is executed via DAO. The examples below were tested against a copy
of the Orders table from the SQL Server version of Northwind to which I
added a field called 'TestField', with a data type of money.

'this code exhibits the problem ...
CurrentProject.Connection.Execute "UPDATE dbo_Orders SET TestField =
NZ(dbo_Orders.Freight, 0) + 25"

'this code does *not* exhibit the problem
CurrentDb.Execute "UPDATE dbo_Orders SET TestField =
NZ(dbo_Orders.Freight, 0) + 25"

As I indicated in an earlier post, the problem is not limited to the NZ
function, I can reproduce it using the IIf function.

'this code exhibits the problem ...
CurrentProject.Connection.Execute "UPDATE dbo_Orders SET TestField =
IIf(dbo_Orders.Freight IS NULL, 0, dbo_Orders.Freight) + 25"

'this code doesn't
CurrentDb.Execute "UPDATE dbo_Orders SET TestField =
IIf(dbo_Orders.Freight IS NULL, 0, dbo_Orders.Freight) + 25"

--
Brendan Reynolds
Access MVP

"TC" <aatcbbtccctc@xxxxxxxxx> wrote in message
news:1142948382.582917.6050@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
As expected, surely? If the first parameter is not null, NZ returns the
value /and type/ of that parameter, no? It's not just a numeric
function ...

Cheers,
TC (MVP Access)
http://tc2.atspace.com





.

Loading