Calculated value not working

[access2010]

=([F5]*[F4])+Sum([F6] + NZ [F7])

We would like to multiply F5 by F4 and then add F6 and F7
All the fields contain values except F7 which may be empty

Could you please advise me as to what we have done wrong?

Enjoy your holidays

Crystal

 

[Eugene-LS]

Could you please advise me as to what we have done wrong?

Missing parentheses when applying the Nz() function ... Nz([F7]; 0)

=([F5]*[F4])+Sum([F6]+Nz([F7]; 0))
or
=([F5]*[F4])+Sum([F6]+Nz([F7]))

 

[access2010]

Perfect Eugene-LS

Thank you
Crystal

 

[access2010]

Thank you Eugene-LS for your assistance, we have had success with your formula, but are having a problem with the formula below.

TT06:=Nz([TY01])*([TP02])+([TK03])+([CC04])+([TA05])

CC04 may be empty so we also have tried
TT06:=Nz([TY01])*([TP02])+([TK03])+Nz([CC04])+([TA05])

Buy the calculation that we are looking for is still wrong, could you please let me know what I have done wrong?

Thank you.
Nicole

 

[XPS35]

Buy the calculation that we are looking for is still wrong, could you please let me know what I have done wrong?

Please provide values, expected result and actual result.

 

[Pat Hartman]

Clean up the code and add NZ to all arguments. Also, keep in mind that the true argument isn't always optional with NZ() so it is therefore best to always supply your desired result to avoid any potential issues. Even though multiplication takes precedence over addition, I enclosed the multiplication in parentheses to ensure that everyone reading the expression would be clear on how it should be evaluated.

TT06:=(Nz(TY01,0) * Nz(TP02,0)) + (Nz(TK03,0) + Nz(CC04,0) + Nz(TA05,0))

When something doesn't work, we have no way of figuring out why unless you provide the values you are working with. Are all rows wrong or just some?

 

[access2010]

Please provide values, expected result and actual result.

Thank you for your note, XPS35

What we are trying to do is;
Total all the values of each Sector, my reference => TS07

Bring the “Each Sector Total” to the Page Footer => TT06
Show the individual “Each Sector % of Total” of all sectors => TSP08

Your assistance will be appreciated.
Nicole
​

 

[Pat Hartman]

You never enclosed CC04 with Nz(). Review post #6 again. If there is ANY possibility a field might be NULL, use Nz() to ensure that a numeric value gets returned.

Defining numeric fields with a default of null is an important concept. Zero is a value so you should be able to tell the difference between 0 and no value. If that isn't relevant to you, then you should probably change the default for the numeric fields to be 0. Although, foreign keys should always be defined as null even though they are numeric.

Also, the join between the two tables is invalid. Who knows what random rows will actually be joined when you get past test data.

A valid join is always a FK in one table to the PK of another. Stock symbol is a data field in both tables. You need to do some work on your schema to fix this.

 

[access2010]

You never enclosed CC04 with Nz(). Review post #6 again. If there is ANY possibility a field might be NULL, use Nz() to ensure that a numeric value gets returned.

Defining numeric fields with a default of null is an important concept. Zero is a value so you should be able to tell the difference between 0 and no value. If that isn't relevant to you, then you should probably change the default for the numeric fields to be 0. Although, foreign keys should always be defined as null even though they are numeric.

Also, the join between the two tables is invalid. Who knows what random rows will actually be joined when you get past test data.

A valid join is always a FK in one table to the PK of another. Stock symbol is a data field in both tables. You need to do some work on your schema to fix this.

Thank you Pat Hartman for your suggestion and now we are receiving the results we want.

Nicole

 

[access2010]

Missing parentheses when applying the Nz() function ... Nz([F7]; 0)

=([F5]*[F4])+Sum([F6]+Nz([F7]; 0))
or
=([F5]*[F4])+Sum([F6]+Nz([F7]))

Thank you Eugene-LS for your assistance and we will be more careful with our brackets in the future.

Nicole