IIF question

[LP]

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))

 

[Ofer]

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)

 

[fredg]

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))

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))

 

[LP]

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?

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))

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))

 

[fredg]

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?

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))

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))

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.