Calculated Fields in Access Query

[mrmarc]

This works for me in Calculated Field Expression:
IIf([Grade]="N2",[NumberOfCases]*28,0)

But this does not:

IIf([Grade]="N2",[NumberOfCases]*28+[LoosePounds],0)

Both Fields "NumberOfCases" and "LoosePounds" are integers which can be Null.

Any Ideas?

 

[sheetspread]

same thing with IIf([Grade]="N2",([NumberOfCases]*28)+[LoosePounds],0) ?

or IIf([Grade]="N2",([NumberOfCases]*28+[LoosePounds]),0)

or IIf([Grade]="N2",(([NumberOfCases]*28)+[LoosePounds]),0)

Probably but I've seen stranger fixes

 

[mrmarc]

Ye none of those worked. Thanks for the try.

Any more ideas?

 

[sheetspread]

Create a field testfield: =45 and see if the syntax works with that

also maybe set the format to standard, and change from integer to double.

 

[Joe4]

IIf([Grade]="N2",[NumberOfCases]*28+[LoosePounds],0)

Both Fields "NumberOfCases" and "LoosePounds" are integers which can be Null.

The Nz function is a great tool for potentially Null numeric values (see: MS Access: Nz Function)

So try this, which converts any Null values to zero so the math can be done:

[COLOR=#333333]IIf([Grade]="N2",Nz([NumberOfCases],0)*28+Nz([LoosePounds],0),0)[/COLOR]

 

[mrmarc]

The Nz function is a great tool for potentially Null numeric values (see: MS Access: Nz Function)

So try this, which converts any Null values to zero so the math can be done:

[COLOR=#333333]IIf([Grade]="N2",Nz([NumberOfCases],0)*28+Nz([LoosePounds],0),0)[/COLOR]

Thanks Joe. But I'm getting an error saying that the expression cannot be used in the expression for a calculated field.

Any more ideas?

 

[mrmarc]

Create a field testfield: =45 and see if the syntax works with that

also maybe set the format to standard, and change from integer to double.

This didnt work either. Thanks though. A calculated field seems to have its limitations.

 

[mrmarc]

Also guys I made a mistake, maybe this will help. This is a calculated field IN a TABLE not a query. Sorry about that.

 

[Joe4]

Thanks Joe. But I'm getting an error saying that the expression cannot be used in the expression for a calculated field.

I re-created the scenario, and it works for me. Based on that, and your other replies, it makes me believe you may not be doing something right.

You are doing this in a query, right?
How exactly are you entering in the Calculated Expression?
Is this a simple Select Query, or an Action Query or Aggregate (Totals) Query?

Please create the query WITHOUT the calculated expression, returning at least all three key fields (Grade, NumberOfCases, and LoosePounds), switch to SQL View, and copy and paste the SQL code here so we can see what your query looks like.

 

[mrmarc]

I re-created the scenario, and it works for me. Based on that, and your other replies, it makes me believe you may not be doing something right.

You are doing this in a query, right?
How exactly are you entering in the Calculated Expression?
Is this a simple Select Query, or an Action Query or Aggregate (Totals) Query?

Please create the query WITHOUT the calculated expression, returning at least all three key fields (Grade, NumberOfCases, and LoosePounds), switch to SQL View, and copy and paste the SQL code here so we can see what your query looks like.

Joe, I'm actually using a calculated field in a table. I'm sorry for the confusion!