Nz Problem in ADO execute

Hi,
I have just discovered an error in some code I have written, and
wondered if anyone can offer an explanation.

I have a linked table 'MYTABLE' which sits on Oracle and is linked via
ODBC. I want to update the value of the 'FINANCE' field, but have to
test for NULL before running the update. My code is shown below:

Set cnn = CurrentProject.Connection

curFeesNew = 50
strSCH = "60809662"

strSQL = "UPDATE MYTABLE " _
& "SET FINANCE = Nz(FINANCE,0)+" _
& curFeesNew & " " _
& "WHERE FIN_ID = '" & strSCH & "';"

cnn.Execute strSQL, lngRecCnt, adCmdText +
adExecuteNoRecords

When I execute this, I do not get any errors, but the value of
'FINANCE' is simply set to 50. For example, if the initial value of
'FINANCE' was 25, I would expect the result to be 75. It seems that Nz
is not interpreted correctly, and worse still it does not throw up an
error.

Strangely, if I surround the NZ statement with a CCur(), then it works
fine i.e:
strSQL = "UPDATE MYTABLE " _
& "SET FINANCE = Ccur(Nz(FINANCE,0))+" _
& curFeesNew & " " _
& "WHERE FIN_ID = '" & strSCH & "';"

I know there are various work arounds possible and the above is only a
small snippet of the overall code, but I would like to know why this
causes an error (my boss wants an explanation as well as a solution).

Can anyone help??
Many thanks
Steve

.

Loading