Blank Field, Null Value

Question

Is there a way to use the Nz function as an expression in a calculated field in a table in MS Access 2010?  I can use the Nz function in Forms and Reports under Control Source but receive the message "The expression Nz([AMOUNT 1]) + Nz([AMOUNT 2) cannot be used in a calculated column." when I try to enter it in Expression Builder for a calculated column.

Answer 1

The Default value will only be applied to newly entered records - not to records already in the table. If zero is an appropriate default value, you could set the Default Value property for future records, and run an update query like

UPDATE mytable

SET [Amount 2] = 0

WHERE [Amount 2] IS NULL;

Back up your database first of course!

Answer 2

Yes, that was the question.

Also, if the answer is no, is there another function that can be used instead?

I'd like the TABLE to retain the consistent information that feeds into the forms and reports.

Answer 3

Is there a way to use the Nz function as an expression in a calculated field in a table in MS Access 2010? 

Are you trying to use a calculated field in a TABLE?

Answer 4

Just tried it again.  Same message.  The lack of a close square bracket was a typo in the above question.  I can double-click on the drop-down menu to pick the field, which automatically populates the field with both square brackets.  Also, there is a different message that pops up for missing brackets or parentheses.

Answer 5

You forgot a close square bracket:

Nz([AMOUNT 1]) + Nz([AMOUNT 2])