Problem with Greater Than

M

mistux

I don't understand what is going on with the math and why this dosen'
work.

I have two fields:[MAX I Design] and [MAX I Calculated] and one calle
[Delta] that has the following as it source:

=IIf([MAX I Calculated]="","",
IIf([MAX I Design]>=[MAX I Calculated],"OK",
IIf([MAX I Calculated]-[MAX I Design]>5000,"ALERT1",
IIf([MAX I Calculated]-[MAX I Design]>4000,"ALERT2",
IIf([MAX I Calculated]-[MAX I Design]>3000,"ALERT3",
IIf([MAX I Calculated]-[MAX I Design]>2000,"ALERT4",
IIf([MAX I Calculated]-[MAX I Design]>0,"ALERT"))))))

12,000 and 10,000 I get "OK"
12,000 and 999 I get nothing, it is just blank
12,000 and 3000 I get "ALERT"
12,000 and 1000 I get "OK"

Why is 1000 >= 12,000 but not 999??
 
M

Marshall Barton

mistux said:
I don't understand what is going on with the math and why this dosen't
work.

I have two fields:[MAX I Design] and [MAX I Calculated] and one called
[Delta] that has the following as it source:

=IIf([MAX I Calculated]="","",
IIf([MAX I Design]>=[MAX I Calculated],"OK",
IIf([MAX I Calculated]-[MAX I Design]>5000,"ALERT1",
IIf([MAX I Calculated]-[MAX I Design]>4000,"ALERT2",
IIf([MAX I Calculated]-[MAX I Design]>3000,"ALERT3",
IIf([MAX I Calculated]-[MAX I Design]>2000,"ALERT4",
IIf([MAX I Calculated]-[MAX I Design]>0,"ALERT"))))))

12,000 and 10,000 I get "OK"
12,000 and 999 I get nothing, it is just blank
12,000 and 3000 I get "ALERT"
12,000 and 1000 I get "OK"

Why is 1000 >= 12,000 but not 999???
Click to expand...


The logic seems ok, but it looks like your values are text
values so they are compared as strings (9xx is greater than
1yyyy).

The other conditions use subtraction so the strings are
(automatically) converted to numeric values and I guess the
comma is getting in the way??
 
R

Rick Brandt

mistux said:
I don't understand what is going on with the math and why this dosen't
work.

I have two fields:[MAX I Design] and [MAX I Calculated] and one called
[Delta] that has the following as it source:

=IIf([MAX I Calculated]="","",
IIf([MAX I Design]>=[MAX I Calculated],"OK",
IIf([MAX I Calculated]-[MAX I Design]>5000,"ALERT1",
IIf([MAX I Calculated]-[MAX I Design]>4000,"ALERT2",
IIf([MAX I Calculated]-[MAX I Design]>3000,"ALERT3",
IIf([MAX I Calculated]-[MAX I Design]>2000,"ALERT4",
IIf([MAX I Calculated]-[MAX I Design]>0,"ALERT"))))))

12,000 and 10,000 I get "OK"
12,000 and 999 I get nothing, it is just blank
12,000 and 3000 I get "ALERT"
12,000 and 1000 I get "OK"

Why is 1000 >= 12,000 but not 999???
Click to expand...

Your "numbers" are actually text and when applying < or > to text the comparison
is strictly one of alphabetical order. Modify your calculated field so that it
returns an actual numeric value and you should be ok.
 
Top