Switch

[shank]

Not getting the results I expect from the below Switch Function.

Given:
[TQM]![acb] = -1529
[TQM]![qstq] = 12261

AOD:
Switch([TQM]![acb]-[TQM]![qstq]>=0,1,
([TQM]![acb]-[TQM]![qstq]*-1)>[TQM]![qstq],2,
(([TQM]![acb]-[TQM]![qstq])*-1)<[TQM]![qstq],3)

Result is AOD = 3

Not making sense... (-1529 - 12261)*-1 is not less than 12261

What am I missing?
thanks!

 

[theDBguy]

Hi shank,

I type the following in the Immediate window and got Null as a result:

?switch(-1529-12261>=0,1,(-1529-12261*-1)>12261,2,((-1529-12261)*-1)<12261,3)
Null

Did I miss something?

 

[Marshall Barton]

Not getting the results I expect from the below Switch Function.

Given:
[TQM]![acb] = -1529
[TQM]![qstq] = 12261

AOD:
Switch([TQM]![acb]-[TQM]![qstq]>=0,1,
([TQM]![acb]-[TQM]![qstq]*-1)>[TQM]![qstq],2,
(([TQM]![acb]-[TQM]![qstq])*-1)<[TQM]![qstq],3)

Result is AOD = 3

Not making sense... (-1529 - 12261)*-1 is not less than 12261

What am I missing?

I think you are missing a set of parensthesis in the second
condition.

OTOH, your condition expressions are not intuitive to me.
Assuming that condition 2 was supposed to be:
(([TQM]![acb]-[TQM]![qstq])*-1)>[TQM]![qstq]
I would prefer to use:
Switch(acb >= qstq,1, acb < 0,2, acb > 0,3)
So I would expect tthe result to be 2 and I have no idea how
you could get 3 unless the data values are not what you said
they are.