IIF question

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

It's been awhile since I've done any Access queries and I need to calculate a
variance on actual sales vs forcast sales for a week. Below is what I've
tried.

SalesPerVar: IIf(([MForcastSales] And
[MActSales])=0,0,IIf(([MForcastSales]=0 And
[MActSales])>0,"NA",([MForcastSales]-[MActSales])/[MForcastSales]*100))
 
I think that the right thing to have, because you can't have devision by 0

SalesPerVar: IIf(nz([MForcastSales],0)=0,0,
(nz([MForcastSales],0)-nz([MActSales],0))/[MForcastSales]*100)
 
It's been awhile since I've done any Access queries and I need to calculate a
variance on actual sales vs forcast sales for a week. Below is what I've
tried.

SalesPerVar: IIf(([MForcastSales] And
[MActSales])=0,0,IIf(([MForcastSales]=0 And
[MActSales])>0,"NA",([MForcastSales]-[MActSales])/[MForcastSales]*100))
Click to expand...

What are you trying to do here?
IIf(([MForcastSales] And [MActSales])=0

1) Is this supposed to literally add the two fields? Then:
IIf([MForcastSales]+[MActSales]=0, 0, .....

2) Are you checking that each field = 0? Then:
IIf([MForcastSales] = 0 And [MActSales]=0, 0, ......

Probably #2.

Note: you also have the parentheses (which are not needed) in the
wrong position in the criteria portion of both IIf's. The closing one,
if you use parentheses, belongs after the =0, not before it.

IIf(([MForcastSales] And [MActSales])=0
should be
IIf(([MForcastSales] And [MActSales]=0)

IIf(([MForcastSales]=0 And [MActSales])> 0
should be
IIf([MForcastSales]=0 And [MActSales]> 0)

Try this:
IIf([MForcastSales] = 0 And [MActSales]= 0, 0, IIf([MForcastSales]=0
And [MActSales] >0, "NA" , ([MForcastSales]-[MActSales])/
[MForcastSales] *100))
 
Hi Thanks for getting back with me.

The test is there for new products or cloesout products. Where it is
expected that the forecasted sales and actual sales could be and are likey
expected to be zero. Yes, yes, yes, number 2 is what I should have done.

Thanks

LP
fredg said:
It's been awhile since I've done any Access queries and I need to calculate a
variance on actual sales vs forcast sales for a week. Below is what I've
tried.

SalesPerVar: IIf(([MForcastSales] And
[MActSales])=0,0,IIf(([MForcastSales]=0 And
[MActSales])>0,"NA",([MForcastSales]-[MActSales])/[MForcastSales]*100))
Click to expand...

What are you trying to do here?
IIf(([MForcastSales] And [MActSales])=0

1) Is this supposed to literally add the two fields? Then:
IIf([MForcastSales]+[MActSales]=0, 0, .....

2) Are you checking that each field = 0? Then:
IIf([MForcastSales] = 0 And [MActSales]=0, 0, ......

Probably #2.

Note: you also have the parentheses (which are not needed) in the
wrong position in the criteria portion of both IIf's. The closing one,
if you use parentheses, belongs after the =0, not before it.

IIf(([MForcastSales] And [MActSales])=0
should be
IIf(([MForcastSales] And [MActSales]=0)

IIf(([MForcastSales]=0 And [MActSales])> 0
should be
IIf([MForcastSales]=0 And [MActSales]> 0)

Try this:
IIf([MForcastSales] = 0 And [MActSales]= 0, 0, IIf([MForcastSales]=0
And [MActSales] >0, "NA" , ([MForcastSales]-[MActSales])/
[MForcastSales] *100))
Click to expand...
 
I have an additional hurdle of handling the "NA" because the field is a
numeric field and I get errors from trying to put a string in a numeric field.

Any thoughts?

fredg said:
It's been awhile since I've done any Access queries and I need to calculate a
variance on actual sales vs forcast sales for a week. Below is what I've
tried.

SalesPerVar: IIf(([MForcastSales] And
[MActSales])=0,0,IIf(([MForcastSales]=0 And
[MActSales])>0,"NA",([MForcastSales]-[MActSales])/[MForcastSales]*100))
Click to expand...

What are you trying to do here?
IIf(([MForcastSales] And [MActSales])=0

1) Is this supposed to literally add the two fields? Then:
IIf([MForcastSales]+[MActSales]=0, 0, .....

2) Are you checking that each field = 0? Then:
IIf([MForcastSales] = 0 And [MActSales]=0, 0, ......

Probably #2.

Note: you also have the parentheses (which are not needed) in the
wrong position in the criteria portion of both IIf's. The closing one,
if you use parentheses, belongs after the =0, not before it.

IIf(([MForcastSales] And [MActSales])=0
should be
IIf(([MForcastSales] And [MActSales]=0)

IIf(([MForcastSales]=0 And [MActSales])> 0
should be
IIf([MForcastSales]=0 And [MActSales]> 0)

Try this:
IIf([MForcastSales] = 0 And [MActSales]= 0, 0, IIf([MForcastSales]=0
And [MActSales] >0, "NA" , ([MForcastSales]-[MActSales])/
[MForcastSales] *100))
Click to expand...
 
I have an additional hurdle of handling the "NA" because the field is a
numeric field and I get errors from trying to put a string in a numeric field.

Any thoughts?

fredg said:
It's been awhile since I've done any Access queries and I need to calculate a
variance on actual sales vs forcast sales for a week. Below is what I've
tried.

SalesPerVar: IIf(([MForcastSales] And
[MActSales])=0,0,IIf(([MForcastSales]=0 And
[MActSales])>0,"NA",([MForcastSales]-[MActSales])/[MForcastSales]*100))
Click to expand...

What are you trying to do here?
IIf(([MForcastSales] And [MActSales])=0

1) Is this supposed to literally add the two fields? Then:
IIf([MForcastSales]+[MActSales]=0, 0, .....

2) Are you checking that each field = 0? Then:
IIf([MForcastSales] = 0 And [MActSales]=0, 0, ......

Probably #2.

Note: you also have the parentheses (which are not needed) in the
wrong position in the criteria portion of both IIf's. The closing one,
if you use parentheses, belongs after the =0, not before it.

IIf(([MForcastSales] And [MActSales])=0
should be
IIf(([MForcastSales] And [MActSales]=0)

IIf(([MForcastSales]=0 And [MActSales])> 0
should be
IIf([MForcastSales]=0 And [MActSales]> 0)

Try this:
IIf([MForcastSales] = 0 And [MActSales]= 0, 0, IIf([MForcastSales]=0
And [MActSales] >0, "NA" , ([MForcastSales]-[MActSales])/
[MForcastSales] *100))
Click to expand...
Click to expand...
While the fields used in the expression may be numeric, the
SalesPerVar column is the result of an expression, and will accept
both number and text.

Here is a sample of data returned using the expression:
Exp: IIf([Dollars]<500,"N/C",[Dollars]/[Qty]*100)
which is close to what you are doing.

Dollars Qty Exp
125 2 N/C
500 3 16666.6666666667
657 -3 -21900
30 9 N/C
2000.5 2 100025
83.3367 2 N/C
625 14 4464.28571428571

As you can see, the column contains both numbers and text.
 
Back
Top