Help with expression builder issue

[trizzytt]

Currently trying to do a simple expression where it adds up all fields.
It works, but if i leave [amount 5] blank for example, it wont calculate everything?

How can i get this to work, even if i wanted to keep some fields blank, as they may not need to be used sometimes.

 

[MarkK]

Each distinct data point in a relational database should occupy its own row. If you find yourself needing to add together multiple fields in the same row, you have probably violated the above rule, and you probably have a table design problem.

For more info on how to structure data for storage in a relational database, google 'database normalization'

For more help on this site you may want to describe your particular problem in greater detail, the payoff being that your math will likely become far simpler if you structure your data correctly, and leverage the power of SQL to do your calculations, and avoid using the expression builder on a single row.

hth
Mark

 

[trizzytt]

Each distinct data point in a relational database should occupy its own row. If you find yourself needing to add together multiple fields in the same row, you have probably violated the above rule, and you probably have a table design problem.

For more info on how to structure data for storage in a relational database, google 'database normalization'

For more help on this site you may want to describe your particular problem in greater detail, the payoff being that your math will likely become far simpler if you structure your data correctly, and leverage the power of SQL to do your calculations, and avoid using the expression builder on a single row.

hth
Mark

Ah i see, so now i set it so all values show £0.00 unless edited it works.

Many thanks!!

 

[Pat Hartman]

You should probably take Mark's advice regarding normalization. This only the first of many issues you'll face over the life of the application.

But to answer the question, As long as you allow nulls in numeric fields - and I'm not suggesting that you shouldn't because in most cases it is important to know the difference between 0 and nothing, you will need to deal with those nulls in arithmetic expressions.

Use the Nz() function to solve the problem.

Nz(a,0) + Nz(b,0) + Nz(c,0) + ...

 

[Uncle Gizmo]

Why should you heed the warnings?

See here:-
http://www.niftyaccess.com/excel-in-access/

And here:-
http://www.niftyaccess.com/excel-in-access/excel-in-access-part-1/

Sent from my SM-G925F using Tapatalk