Using NZ in a table calculated field

[andrewaldrich]

Access 2013

I am trying to add some numbers together for a number of fields, of which at least one field will either be 0 or empty. I am trying to use the following formula in a calculated field in a table:

IIf(IsNull([Price Increase %]),[Total],([Total]*[Price Increase %])+Nz([LumpSumAnnualIncrease]))

Access returns an error statement: The expression XXX cannot be used in a calculated column.

Is there a different formula I should be using, or a work around, other than using the formula in a query.

Thanks

 

[SOS]

And finally, I don't use calculated fields in tables. As with many crutches, they actually cause problems once you get good enough to write queries and VBA. I also believe there is a bug with them. If you change the calculation, I don't believe that all existing rows are recalculated so you could end up with invalid data.

The one problem with Web databases are that you need to use calculated columns essentially because there are things they can't do which one would normally do otherwise. I don't use web databases so it doesn't affect me and therefore I don't remember all of the appropriate caveats.

 

[SOS]

Regardless, they are still limited to SQL functions ONLY. Web databases are still way too limited for usefulness for anything but the simplest applications.

Yes, that is true about the SQL Functions. And, while the 2013 Web databases are more functional and can create more complex ones than one could in 2010, they are seriously limited. I wonder if MS will ever get it right. But then again, maybe if one needs a more complex one, ASP.NET would most likely be a better option.