Using "Null" in an IIf statement

G

Guest

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

Guest

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

Guest

Thank you very much!!!!!

Klatuu said:
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 said:
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])
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Enter Parameter Value Message Pops Up 4
IIF statement is not working 1
Using IIf null statement in a query 5
Iif statement with Nulls not working 2
Access query with IIF statements is null for a field and not null 3
Combine IIf with NZ to get null for zero calculated sum? 3
IIF Statement using Is Null and is Not Null 3
Help! Trouble converting values with IIF statement 1

Top