SQL Update and Set one column value by Subtracting two column values from same table

[Mposwal]

Hi All,
I am facing some strange type of error on performing a simple task,
What i am trying to set a column value (Vol3) by subtracting Vol2 column from Vol1 column of same table by using the following query (using ADO Connection and recordset):

rs2.Open "Update " & SchemeName & " Set Vol1=Volume ;", conn2
rs2.Open "Update " & SchemeName & " Set Vol2=Volume Where trim(REBGRP)='" & Prod1 & "';", conn2
rs2.Open "Update " & SchemeName & " Set Vol3=(vol1-vol2);", conn2

the problem is that my first two update queries are working perfectly and setting vol1 and vol2 values, but third query returning blank (0 for some cases where vol1=vol2).
I have spend almost many hours for trouble shoot the same but all in vain.
Any help will be appreciated.

 

[Mposwal]

Hi All,
I am facing some strange type of error on performing a simple task,
What i am trying to set a column value (Vol3) by subtracting Vol2 column from Vol1 column of same table by using the following query (using ADO Connection and recordset):

rs2.Open "Update " & SchemeName & " Set Vol1=Volume ;", conn2
rs2.Open "Update " & SchemeName & " Set Vol2=Volume Where trim(REBGRP)='" & Prod1 & "';", conn2
rs2.Open "Update " & SchemeName & " Set Vol3=(vol1-vol2);", conn2

the problem is that my first two update queries are working perfectly and setting vol1 and vol2 values, but third query returning blank (0 for some cases where vol1=vol2).
I have spend almost many hours for trouble shoot the same but all in vain.
Any help will be appreciated.

for more information I am using Access 365 and excel 365

 

[FormR]

but third query returning blank

Is either Vol1 or Vol2 null for the records returning blanks? Does it work when both Vol1 and Vol2 contain values?

(0 for some cases where vol1=vol2)

Isn't that the correct result?

 

[Mposwal]

Is either Vol1 or Vol2 null for the records returning blanks? Does it work when both Vol1 and Vol2 contain values?



Isn't that the correct result?

Yes, Vol2 was setting null hence was effecting the vol3. Thanks, So I have to update NULL with zeros in Vol2 before updating Vol3. THANKS for you quick effort. Love from INDIA

 

[FormR]

So I have to update NULL with zeros in Vol2 before updating Vol3.

Yes, you can do that - or you could try : rs2.Open "Update " & SchemeName & " Set Vol3=vol1-Nz(vol2,0);", conn2