Using NZ in a table calculated field (1 Viewer)

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

Calculated columns have very limited usefulness since they can only include functions known to SQL. VB/VBA/UDF functions are not allowed.

In your particular case, you could replace the Nz() with an IIF(). But it is not always possible to find a substitute function.

PS - best practice column/table names include ONLY the letters a-z and A-Z, the numbers 0-9, and the underscore. Your names are non-conforming and will force you to always encase them in square brackets. Also, you will find if you ever create control events for them that VBA renames them and replaces all offending characters with underscores. You'll end up with procedures named Price_Increase__. Also, if you also had a column named [Price Increase $] (using your naming scheme), VBA would NOT be able to create any procedures since the name would duplicate the name generated for the % field.

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.

Pat Hartman said:

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.

Click to expand...

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.

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

Pat Hartman said:

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

Click to expand...

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.

The weak Access web solution really bothers me. Access 1.0 was a fully functional application creation tool. And four versions later (starting with DAP in A2003), MS still does not have a fully functional web solution based on Access. It has gotten much closer with A2013 but it still lacks basic application features. And forcing developers to use macros rather than a real programming language limits it to power user status at best. And wedding it to SharePoint limits its usefulness to my smaller clients and completely eliminates its use as an outward facing application.