Comparison of Signs

  • Thread starter Thread starter Diane
  • Start date Start date
D

Diane

In a query, I need to compare to fields (numbers) and
compare if the signs are the same (ie, positive or
negative)

Example:

Field1 Field2 Result
100 -100 No
100 100 Yes

There is a 'Sign' function in Excel that handles this.
Is there an easier way in Access to accomplish this
rather than using a lengthly IIF statement?

Thanks for the help and consideration.
 
Diane

I'm not aware of a "Sign()" function (but that doesn't mean much <g>). An
alternate approach would be to create a new field in which you subtract one
from the other, and as a criterion, look for "0" (or "not 0", depending on
what you are seeking).

If you want the query to return "Yes" or "No", using the IIF() statement is
relatively simple:

YourNewField: IIF([YourFirstNumber] - [YourSecondNumber] =
0,"Yes","No")

should generate the "yes"/"no" without displaying any of the numbers.
 
There is a Sgn() function in VBA that returns 1 if the value is positive, -1
if it's negative.
 
Jeff said:
Thanks, Ken!

(learn something new every day!)
Click to expand...

Which means Diane could use an expression like this to get a Yes/No
column (instead of IIf() function):

SELECT Format(Sgn(Field1)=Sgn(Field2), "Yes/No") As Result ...
 
OR

IIF(Field1*Field2>0,True,False)

Of course, there is a problem with zero. Is Zero Positive, Negative, Both. On
a math line, it is the dividing point between the two.

IF you wish to treat it as both, change the above to

IIF(Field1*Field2>=0,True,False)
 
Back
Top