Using "Null" in an IIf statement

[Rob]

Hello,
I am trying to build a calculation in a query where if a field is blank,
return a blank or run the calculation. Below is the IIf statement I am using.
Even though [SumOfTotal FY06 Expenses] is blank the formula is still running
the calculation instead of producing a "blank".
Should I use something other than "Null"? I have tried "", but get an error
message.

MTD Roaster Yield: IIf([SumOfTotal FY06 Variable
Expenses]=Null,Null,-([SumOfRoasted Coffee Volumes]-([AvgOfRoaster
Yield]*[SumOfGreen Coffee Volumes]))*[AvgOfStandard Cost per LB])

 

[Klatuu]

It is a syntax problem. nothing is ever = Null.
In VBA, you use the IsNull() function
In SQL you use IS NULL or IS NOT NULL

MTD Roaster Yield: IIf([SumOfTotal FY06 Variable Expenses] IS Null, Null,
-([SumOfRoasted Coffee Volumes]-([AvgOfRoaster Yield]*[SumOfGreen Coffee
Volumes]))*[AvgOfStandard Cost per LB])

 

[Rob]

Thank you very much!!!!!

It is a syntax problem. nothing is ever = Null.
In VBA, you use the IsNull() function
In SQL you use IS NULL or IS NOT NULL

MTD Roaster Yield: IIf([SumOfTotal FY06 Variable Expenses] IS Null, Null,
-([SumOfRoasted Coffee Volumes]-([AvgOfRoaster Yield]*[SumOfGreen Coffee
Volumes]))*[AvgOfStandard Cost per LB])

Hello,
I am trying to build a calculation in a query where if a field is blank,
return a blank or run the calculation. Below is the IIf statement I am using.
Even though [SumOfTotal FY06 Expenses] is blank the formula is still running
the calculation instead of producing a "blank".
Should I use something other than "Null"? I have tried "", but get an error
message.

MTD Roaster Yield: IIf([SumOfTotal FY06 Variable
Expenses]=Null,Null,-([SumOfRoasted Coffee Volumes]-([AvgOfRoaster
Yield]*[SumOfGreen Coffee Volumes]))*[AvgOfStandard Cost per LB])