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

Mposwal

New Member
Joined
Jul 22, 2022
Messages
33
Office Version
  1. 365
  2. 2021
Platform
  1. Windows
  2. Mobile
  3. Web
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.
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
Solution

Forum statistics

Threads
1,213,517
Messages
6,114,089
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top