IIF Issue in Query

  • Thread starter Thread starter mattc66 via AccessMonster.com
  • Start date Start date
M

mattc66 via AccessMonster.com

If the PercChg is 5% up or down I want it to have a -1 in Expr2 column.

This is my formula in the query.
Expr2: IIf([PercChg]=1,0,[PercChg]>=[PercFactor]) Or [PercChg]<=[PercFactor]

It's not working correct, the rows with -3.55% and 3.51% should be 0. Can
anyone help me with the proper syntax?

PercFactor PercChg Expr2
5.00% 7.50% -1
5.00% -5.54% -1
5.00% -3.55% -1
5.00% 3.51% -1
5.00% -28.57% -1
5.00% 100.00% 0

Thanks
Matt
 
Try Abs() to avoid it being negative. Why do you think that -3.55 or 3.51
would evaluate as = 1?

HTH;

Amy
 
If the PercChg is 5% up or down I want it to have a -1 in Expr2 column.

This is my formula in the query.
Expr2: IIf([PercChg]=1,0,[PercChg]>=[PercFactor]) Or [PercChg]<=[PercFactor]

It's not working correct, the rows with -3.55% and 3.51% should be 0. Can
anyone help me with the proper syntax?

PercFactor PercChg Expr2
5.00% 7.50% -1
5.00% -5.54% -1
5.00% -3.55% -1
5.00% 3.51% -1
5.00% -28.57% -1
5.00% 100.00% 0
Click to expand...

Try without using IIF at all: you can just have an expression which
evaluates to True (-1) or False (0):

Expr2: Abs([PercChg]) > [PercFactor]


John W. Vinson[MVP]
 
That worked.. Thanks


John said:
If the PercChg is 5% up or down I want it to have a -1 in Expr2 column.
Click to expand...
[quoted text clipped - 11 lines]
5.00% -28.57% -1
5.00% 100.00% 0
Click to expand...

Try without using IIF at all: you can just have an expression which
evaluates to True (-1) or False (0):

Expr2: Abs([PercChg]) > [PercFactor]

John W. Vinson[MVP]
Click to expand...
 
Back
Top