Access is writing numbers next to each other instead of adding them up

Question

I want to add up some list fields that are filled with numbers from a table if I press a button. For debugging I have chosen easy values.

The first field below should contain the mean value of all fields, the second one should contain the sum, and the third one should contain the sum of each column multiplied by a certain percentage and then summed (formula & pictures below).

By default, if no percentage values are given, it should only show the sum.

Here is the Problem:

Everything works fine and as intended, with or without percentage given:

Except when I enter values or a value into row number 2:

.

Then, the field that normally shows the sum (correct for every other row) just puts every number of every row next to each other instead of summing them. This also happens if a percentage is given.

If I empty row 2, it works correctly again.

I cannot explain why this happens, does anyone have an idea why?

The formula for the last field is:

(CB is Column 1, OF is Column 2 and AS is Column 3, and [FehlleistungXX] is the Field with the percent value:)

=0+Nz([DeltaDetailCB1]+[DeltaDetailCB2]+[DeltaDetailCB3]+[DeltaDetailCB4]+[DeltaDetailCB5]+[DeltaDetailCB6]+[DeltaDetailCB7]+[DeltaDetailCB8]+[DeltaDetailCB9]+[DeltaDetailCB10]+[DeltaDetailCB11]+[DeltaDetailCB12]+[DeltaDetailCB13]+[DeltaDetailCB14]+[DeltaDetailCB15]+[DeltaDetailCB16];0)*Nz(1+[FehlleistungCB]*0,01;0)

+Nz([DeltaDetailAS1]+[DeltaDetailAS2]+[DeltaDetailAS3]+[DeltaDetailAS4]+[DeltaDetailAS5]+[DeltaDetailAS6]+[DeltaDetailAS7]+[DeltaDetailAS8]+[DeltaDetailAS9]+[DeltaDetailAS10]+[DeltaDetailAS11]+[DeltaDetailAS12]+[DeltaDetailAS13]+[DeltaDetailAS14]+[DeltaDetailAS15]+[DeltaDetailAS16];0)*Nz(1+[FehlleistungAS]*0,01;0)

+Nz([DeltaDetailOF1]+[DeltaDetailOF2]+[DeltaDetailOF3]+[DeltaDetailOF4]+[DeltaDetailOF5]+[DeltaDetailOF6]+[DeltaDetailOF7]+[DeltaDetailOF8]+[DeltaDetailOF9]+[DeltaDetailOF10]+[DeltaDetailOF11]+[DeltaDetailOF12]+[DeltaDetailOF13]+[DeltaDetailOF14]+[DeltaDetailOF15]+[DeltaDetailOF16];0)*Nz(1+[FehlleistungOF]*0,01;0)

Edit 1: I tried deleting the Fields and replacing them with copies from the other fields, that did not solve the problem.

Answer 1

Firstly, I would suggest enclosing each of the fields within an Nz expression, rather than selectively enclosing calculated expressions within Nz expressions, i.e.:

=
(
    (
        Nz([DeltaDetailCB1],0)+
        Nz([DeltaDetailCB2],0)+
        Nz([DeltaDetailCB3],0)+
        Nz([DeltaDetailCB4],0)+
        Nz([DeltaDetailCB5],0)+
        Nz([DeltaDetailCB6],0)+
        Nz([DeltaDetailCB7],0)+
        Nz([DeltaDetailCB8],0)+
        Nz([DeltaDetailCB9],0)+
        Nz([DeltaDetailCB10],0)+
        Nz([DeltaDetailCB11],0)+
        Nz([DeltaDetailCB12],0)+
        Nz([DeltaDetailCB13],0)+
        Nz([DeltaDetailCB14],0)+
        Nz([DeltaDetailCB15],0)+
        Nz([DeltaDetailCB16],0)
    ) *
    (1+(Nz([FehlleistungCB],0)*0.01))
)
+
(
    (
        Nz([DeltaDetailAS1],0)+
        Nz([DeltaDetailAS2],0)+
        Nz([DeltaDetailAS3],0)+
        Nz([DeltaDetailAS4],0)+
        Nz([DeltaDetailAS5],0)+
        Nz([DeltaDetailAS6],0)+
        Nz([DeltaDetailAS7],0)+
        Nz([DeltaDetailAS8],0)+
        Nz([DeltaDetailAS9],0)+
        Nz([DeltaDetailAS10],0)+
        Nz([DeltaDetailAS11],0)+
        Nz([DeltaDetailAS12],0)+
        Nz([DeltaDetailAS13],0)+
        Nz([DeltaDetailAS14],0)+
        Nz([DeltaDetailAS15],0)+
        Nz([DeltaDetailAS16],0)
    ) *
    (1+(Nz([FehlleistungAS],0)*0.01))
)
+
(
    (
        Nz([DeltaDetailOF1],0)+
        Nz([DeltaDetailOF2],0)+
        Nz([DeltaDetailOF3],0)+
        Nz([DeltaDetailOF4],0)+
        Nz([DeltaDetailOF5],0)+
        Nz([DeltaDetailOF6],0)+
        Nz([DeltaDetailOF7],0)+
        Nz([DeltaDetailOF8],0)+
        Nz([DeltaDetailOF9],0)+
        Nz([DeltaDetailOF10],0)+
        Nz([DeltaDetailOF11],0)+
        Nz([DeltaDetailOF12],0)+
        Nz([DeltaDetailOF13],0)+
        Nz([DeltaDetailOF14],0)+
        Nz([DeltaDetailOF15],0)+
        Nz([DeltaDetailOF16],0)
    ) *
    (1+(Nz([FehlleistungOF],0)*0.01))
)

My suspicion would be that the value returned by the Nz function was being interpreted as a string with the addition operator(+) then used to concatenate the string returned by each Nz expression.

If my code yields the same result, you can force the data type coercion by enclosing the entire expression within CDbl().

However, I must say that repeating this many fields in a single expression is a red flag that you have a poorly designed database.