Query with negative values

[Guest]

I hv this table

PRTYPE MTHS NET_BAL
A 9 -6160.00
A 6 4757.56
A 9 86.82

When i use this expression in my query

Abs(sum(([prtype]="A" And [mths]>5)*[net_bal]))

i expect the sum to be -1315.62 but i get 1315.62 (positive)

What could be wrong...

Thanks

 

[Guest]

The ABS() function returns the absolute value of your result (absolute value
is a number without a sign). If you are expecting the result to be signed
(negative or positive), you would need to remove the ABS() function from your
expression.

I hope this helps!
Mitch

 

[John W. Vinson]

I hv this table

PRTYPE MTHS NET_BAL
A 9 -6160.00
A 6 4757.56
A 9 86.82

When i use this expression in my query

Abs(sum(([prtype]="A" And [mths]>5)*[net_bal]))

i expect the sum to be -1315.62 but i get 1315.62 (positive)

What could be wrong...

Thanks

The Abs() function returns the Absolute Value of its argument: if the
argument is positive it returns it unchanged, if it is negative it
returns the positive value of equal magnitude.

Abs(<anything>)

will therefore always return a positive number. That's what the
function is designed to do.

Just remove the Abs( ) and you should get positive or negative sums
based on the data.

John W. Vinson [MVP]

 

[Guest]

I hv removed Abs but still returned positive

This is my exp, sum(([prtype]="A" And [mths]>5)*[net_bal])

I hv this table

PRTYPE MTHS NET_BAL
A 9 -6160.00
A 6 4757.56
A 9 86.82

When i use this expression in my query

Abs(sum(([prtype]="A" And [mths]>5)*[net_bal]))

i expect the sum to be -1315.62 but i get 1315.62 (positive)

What could be wrong...

Thanks

The Abs() function returns the Absolute Value of its argument: if the
argument is positive it returns it unchanged, if it is negative it
returns the positive value of equal magnitude.

Abs(<anything>)

will therefore always return a positive number. That's what the
function is designed to do.

Just remove the Abs( ) and you should get positive or negative sums
based on the data.

John W. Vinson [MVP]

 

[Guest]

I hv removed Abs but still returned positive

This is my exp, sum(([prtype]="A" And [mths]>5)*[net_bal])

The ABS() function returns the absolute value of your result (absolute value
is a number without a sign). If you are expecting the result to be signed
(negative or positive), you would need to remove the ABS() function from your
expression.

I hope this helps!
Mitch

I hv this table

PRTYPE MTHS NET_BAL
A 9 -6160.00
A 6 4757.56
A 9 86.82

When i use this expression in my query

Abs(sum(([prtype]="A" And [mths]>5)*[net_bal]))

i expect the sum to be -1315.62 but i get 1315.62 (positive)

What could be wrong...

Thanks

 

[Guest]

Others have correctly mentioned the ABS function. I'm also betting that this
part of your expression is returning -1 as I got something similar to return
with my test data.

sum([prtype]="A" And [mths]>5)

I really don't understand what you are trying to do. Summing a text field
And a mths field? It looks to me like both of them need to be in the criteria
such as

SELECT sum([net_bal])
FROM YourTable
WHERE [prtype]="A"
AND [mths]>5) ;

 

[Douglas J. Steele]

Jerry: zyus is using

sum(([prtype]="A" And [mths]>5)*[net_bal])

not

sum([prtype]="A" And [mths]>5)

As you note, ([prtype]="A" And [mths]>5) will return -1 if both conditions
are true, or 0 otherwise. By multiplying that value times the value of
[net_ball], you're adding together the negative of [net_ball] for all rows
for which both conditions are true. The end result should be the same as
using your SQL.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Others have correctly mentioned the ABS function. I'm also betting that
this
part of your expression is returning -1 as I got something similar to
return
with my test data.

sum([prtype]="A" And [mths]>5)

I really don't understand what you are trying to do. Summing a text field
And a mths field? It looks to me like both of them need to be in the
criteria
such as

SELECT sum([net_bal])
FROM YourTable
WHERE [prtype]="A"
AND [mths]>5) ;
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I hv this table

PRTYPE MTHS NET_BAL
A 9 -6160.00
A 6 4757.56
A 9 86.82

When i use this expression in my query

Abs(sum(([prtype]="A" And [mths]>5)*[net_bal]))

i expect the sum to be -1315.62 but i get 1315.62 (positive)

What could be wrong...

Thanks

 

[John W. Vinson]

I hv removed Abs but still returned positive

This is my exp, sum(([prtype]="A" And [mths]>5)*[net_bal])

Ok... [prtype] = "A" is a logical expression which will return -1 if
it is True, and 0 if it is False.

[mths] > 5 is another logical expression, which will return -1 if it
is True and 0 if it is False.

You're doing a logical AND of these two expressions, which will return
-1 if both are True and 0 if either or both are False.

So you're either multiplying [Net_Bal] by 0 or by -1.

If Net_ bal is a negative number, multiplying it by -1 will change its
sign to positive.

You're getting what you're ASKING for... which I admit may not be what
you want!

How about moving the Abs():

sum(Abs(([prtype]="A" And [mths]>5))*[net_bal])

to multiply net_bal by either 0 or +1. Does that get the desired
result?

John W. Vinson [MVP]

 

[Guest]

Well I certainly learned something new today! I don't think that I'd ever
would have even considered doing something like that.

Thanks for the explanation.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Jerry: zyus is using

sum(([prtype]="A" And [mths]>5)*[net_bal])

not

sum([prtype]="A" And [mths]>5)

As you note, ([prtype]="A" And [mths]>5) will return -1 if both conditions
are true, or 0 otherwise. By multiplying that value times the value of
[net_ball], you're adding together the negative of [net_ball] for all rows
for which both conditions are true. The end result should be the same as
using your SQL.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Others have correctly mentioned the ABS function. I'm also betting that
this
part of your expression is returning -1 as I got something similar to
return
with my test data.

sum([prtype]="A" And [mths]>5)

I really don't understand what you are trying to do. Summing a text field
And a mths field? It looks to me like both of them need to be in the
criteria
such as

SELECT sum([net_bal])
FROM YourTable
WHERE [prtype]="A"
AND [mths]>5) ;
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I hv this table

PRTYPE MTHS NET_BAL
A 9 -6160.00
A 6 4757.56
A 9 86.82

When i use this expression in my query

Abs(sum(([prtype]="A" And [mths]>5)*[net_bal]))

i expect the sum to be -1315.62 but i get 1315.62 (positive)

What could be wrong...

Thanks

 

[Guest]

Hi John,

With this exp

sum(Abs(([prtype]="A" And [mths]>5))*[net_bal])

I can get my desired result (-1315.62). Do i hv to multiply by either 0 or
+1 again?

Thanks

I hv removed Abs but still returned positive

This is my exp, sum(([prtype]="A" And [mths]>5)*[net_bal])

Ok... [prtype] = "A" is a logical expression which will return -1 if
it is True, and 0 if it is False.

[mths] > 5 is another logical expression, which will return -1 if it
is True and 0 if it is False.

You're doing a logical AND of these two expressions, which will return
-1 if both are True and 0 if either or both are False.

So you're either multiplying [Net_Bal] by 0 or by -1.

If Net_ bal is a negative number, multiplying it by -1 will change its
sign to positive.

You're getting what you're ASKING for... which I admit may not be what
you want!

How about moving the Abs():

sum(Abs(([prtype]="A" And [mths]>5))*[net_bal])

to multiply net_bal by either 0 or +1. Does that get the desired
result?

John W. Vinson [MVP]

 

[John W. Vinson]

Hi John,

With this exp

sum(Abs(([prtype]="A" And [mths]>5))*[net_bal])

I can get my desired result (-1315.62). Do i hv to multiply by either 0 or
+1 again?

Huh???

You get the desired result. Why do anything more?

Multiplying by 0 will give you 0 for all values of the fields.
Multiplying by 1 will give you the exact same result unchanged.

John W. Vinson [MVP]