R
Random
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
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