functions to add or substract two valus

[grad2009]

Hi all,

I want to create a query. In this query I want to add two values and substract the result from a third number but i can't do that. I used the mathmatics operators(+, - ) but it does not work. are there any functions can do that? please help me.

Thank you

 

[vbaInet]

The subtract would work if the two fields you are subtracting are of a Number type.

Expr1: [Total] - [Subtotal]

 

[Brianwarnock]

Or
Fieldc-(fielda+fieldb)

There must be more to this.

Brian

 

[naina]

Hi
Here is my SP Where you can find your answer ,perhaps.
You can turn onto Function easily

*******************
CREATE PROCEDURE DBO.CodeBalance

@FiscalYear AS INT,
@Itemcode AS INT

AS

SET NOCOUNT ON
--SET ANSI_NULLS ON
--SET QUOTED_IDENTIFIER ON

BEGIN


DECLARE @inflow INT
DECLARE @outflow INT
DECLARE @credit INT
DECLARE @debit INT

SET @inflow=(SELECT ISNULL(SUM(Amount),0)
FROM FisCalYearDonorBudgetT
WHERE FisCalYear=@FiscalYear
AND DACode=@Itemcode)

SET @outflow=(SELECT ISNULL(SUM(Amount),0)
FROM DonorTranSmasterT
WHERE FisCalYear=@FiscalYear
AND ItemiD=@Itemcode)

SET @credit=(SELECT ISNULL(SUM(Credit),0)
FROM DonorBudgetAdjustT
WHERE FisCalYear=@FiscalYear
AND ItemCr=@Itemcode)

SET @debit=(SELECT ISNULL(SUM(Debit),0)
FROM DonorBudgetAdjustT
WHERE FisCalYear=@FiscalYear
AND ItemDr=@Itemcode)

SELECT (((@inflow-@outflow)-@credit)+@debit) AS CodeBalance
END
GO
********
Naina

 

[vbaInet]

 

[grad2009]

Thank you all,
the substraction operation [Total] - [Subtotal] is done ok, but when i tried to do the adding operation Fieldc-(fielda+fieldb) there were no results. naina's answer is complex. are there any easy solution please?

Thank you again

 

[vbaInet]

If you remember I asked if all three fields are of type Number? Are you positive that fieldc has a value?

Show exactly what you wrote.

 

[grad2009]

Thank you vbaInet for your fast reply. There is no value in fieldb, but there are some values in feilda and fieldc.However, there are no results.

 

[vbaInet]

Still one question left unanswered?

Actually, make that two.

 

[grad2009]

Still one question left unanswered?

I am sorry, which question?

Actually, make that two.

sorry, I can't understand what do you mean?

 

[vbaInet]

1. Are all your fields of type Number? Like Integer, Double etc...

2. Can you please show the expression you are using

 

[grad2009]

Thank you vbaInet very much for your explination.

Are all your fields of type Number? Like Integer, Double etc...

yes they are.

Can you please show the expression you are using

sure here it is

SELECT Customers.customerNo, Customers.customerName, (totalPayment.totalPayment+totalSales.totalSales)-totalReceived.totalReceived AS customerBalance
FROM ((Customers INNER JOIN totalPayment ON Customers.customerNo = totalPayment.customerNo) INNER JOIN totalReceived ON Customers.customerNo = totalReceived.customerNo) INNER JOIN totalSales ON Customers.customerNo = totalSales.customerNo;

 

[JamesMcS]

This isn't anything to do with nulls not adding up is it? I had a similar problem a while back while adding up 40 or so fields, in the end I had to use cint(nz([field],0)) in all the fields I wanted to add up, otherwise I'd get blanks unless every field had a value in it.

 

[JamesMcS]

And, for the grossly undereducated among us (me), what language is the 4th post in? I've never seen syntax like that before in Access... "GO" sounds too simple for MS anyway

 

[Lightwave]

I think its an SQL stored procedure?

 

[vbaInet]

SELECT Customers.customerNo, Customers.customerName, ((Nz(totalPayment.totalPayment,0)+Nz(totalSales.totalSales, 0))-Nz(totalReceived.totalReceived, 0)) AS customerBalance
FROM ((Customers INNER JOIN totalPayment ON Customers.customerNo = totalPayment.customerNo) INNER JOIN totalReceived ON Customers.customerNo = totalReceived.customerNo) INNER JOIN totalSales ON Customers.customerNo = totalSales.customerNo;

I believe you need to wrap the whole calculated field in brackets so that becomes your alias, plus you needed the Nz() function too as JamesMcS pointed out. Try the above.

 

[grad2009]

Thank you all, Thank you JamesMcS, Thank you vbaInet. you are really helpful.
Now it is ok.
Thank you again

 

[vbaInet]

You're welcome. Glad to have made some input.