problem with calculated fields in Access 2016

[Elfi]

Hello everyone,

I am facing a problem in Access, that by now gives me some headache, so I am hoping for some help from you guys.

I am working in a small company, that is renting skylifts. We do our rental contracts in Access, and now it is my duty to get out from theses contracts the according invoice for our clients.
Therefore I am working now with calculated fields, which worked quite well until this moment. So I have 6 fields in my database (all of them are already calculated fields) as seen below.

[Bühnenpreis_gesamt]+[Versicherung_gesamt]+[Transportkosten_gesamt]+[Sonderposten_gesamt]+[Sonderposten#2_gesamt]+[Sonderposten#3_gesamt]

Now I want to add these fileds together, which is not working, because some of the fields are without value. After some research I have tried this:

Nz([Bühnenpreis_gesamt];0)+Nz([Versicherung_gesamt];0)+Nz([Transportkosten_gesamt];0)+Nz([Sonderposten_gesamt];0)+Nz([Sonderposten#2_gesamt];0)+Nz([Sonderposten#3_gesamt];0)

Access is telling me now, that this expression can not be used in a calculated column.



Can anyone help me fixing this problem? Thank you very much. I appreciate it.

[CJ_London]

you cannot use functions such as nz in a calculated field.

Although access provides the functionality, in reality they are usually more trouble than they are worth. - You should not really be storing calculated values, but calculate them when you need them in a query

[Elfi]

Thank you for the quick reply Ajax. My aim was though to merge the values of these fields from access table view into a word document that is an invoice template. Is there no possibility to calculate fields that have no value inside of them?
I could of course put them on "0", but then I have on every invoice the issue that I must edit away these "0`s" in the word doc.

[CJ_London]

Is there no possibility to calculate fields that have no value inside of them?

within a table no - as suggested, use a query rather than a table.

select Nz([Bühnenpreis_gesamt];0)+Nz([Versicherung_gesamt];0)+Nz([Transportkosten_gesamt];0)+Nz([Sonderposten_gesamt];0)+Nz([Sonderposten#2_gesamt];0)+Nz([Sonderposten#3_gesamt];0) as calcfield

From myTable

[Elfi]

Ok, will do that! ...and probably be back. Thank you Ajax.