Subtract positive number from negative number

I have a query similar to this:

SELECT Sales.ThisYear - Sales.LastYear AS SalesDifference
FROM ThisYear, LastYear;

This works fine if they are both positive. However, I have an instance
where the sales for this year is negative and last year is positive:

ThisYear: -80,000
LastYear: 20,000

Result in Access: -100,000 when it should be -60,000.

So how can I get it to subtract a positive number from a negative
number?

What kind of business rule would result in -60,000 rather than the correct
-100,000?

--
Duane Hookom
Microsoft Access MVP


"Brett" wrote:

I have a query similar to this:

SELECT Sales.ThisYear - Sales.LastYear AS SalesDifference
FROM ThisYear, LastYear;

This works fine if they are both positive. However, I have an instance
where the sales for this year is negative and last year is positive:

ThisYear: -80,000
LastYear: 20,000

Result in Access: -100,000 when it should be -60,000.

So how can I get it to subtract a positive number from a negative
number?

That was just an example. You could have the first month of the year
with a credit on account and comparing that with the first month of
last year, you have this scenario. So instead of analyzing this in
terms of business, can you answer my question?

Essentially, what I am looking for is a Variance function of some sort
that will show the difference between two numbers, does it exist?

Never mind...I'm using the Abs() function and it worked.

Not wishing to incur your wrath further I don't understand why you have
negative sales ???

"Brett" wrote:

I have a query similar to this:

SELECT Sales.ThisYear - Sales.LastYear AS SalesDifference
FROM ThisYear, LastYear;

This works fine if they are both positive. However, I have an instance
where the sales for this year is negative and last year is positive:

ThisYear: -80,000
LastYear: 20,000

Result in Access: -100,000 when it should be -60,000.

So how can I get it to subtract a positive number from a negative
number?