Default values in fields used for calculation?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a number of fields that are added up to give sub-totals and totals in
other fields.
It appears that if any of those individual fields is blank, the expressions
in the SubTotal and Total fields (to add all those values) will not work.
It looks as though I must set the default to 0 in each of the fields, for
the calculation to work.
This is a bit of a pain as there are lots of them and it will be quite a bit
of work to go through them all adding a default value, so I thought I'd check
before starting on this exciting little (not so little!) task.
Thanks
CW
 
The Nz function is used to test for Null and return a specified value if Null
is encountered. It the value being tested in not Null, it returns the
original value:

x= Nz(Me.SomeControl, 0)

If Me.SomeControl has a value, x will now contain that value
If Me.SomeControl contains Null, x will now be 0.

It is very useful for using in calculations when you want to ignore Nulls.
Now, there is one thing you need to be aware of. You want to enclose each
item in the calculation in the Nz function, not the results. If you wrap the
calculation in the Nz function and any item in the calculation is Null, you
will not get the results of the calculation, you will get the replacement
value. For Example

x = Nz(y + z, 0)
If either y or z is Null, x will = 0
x = Nz(y,0) + Nz(z,0)
If either y or z is Null, x will = the value of the one that is not Null.
 
CW,

You can find help on the Nz and any other VBA function in VBA Help. Press
F11 to load the VBA Editor, and select Help.

Nz avoids the error caused by attempting to do arithmetic with a Null value
by converting it to a value of zero (or any other value that you specify in
its optional parameter). So, to add two values, either of which might be
Null, set the ControlSource of your calculated control to:

=Nz([FirstValue]) + Nz([SecondValue])

Also, I hope that when you say, "subtotals and totals in other *fields*"
that what you really mean is "subtotals and totals in other *form controls*".
As is often discussed on this forum, calculated values should, with very
rare exception, should be calculated on-the-fly in a query upon which the
form is based or in a form control, rather than storing it in an underlying
field using code.

To do so is slower, wastes disk space, and risks inaccurate data should a
user change a value involved in the calculation outside the context of your
form.

Hope that helps.
Sprinks
 
My rule on using stored calculations is that it is only allowed when it is
raining in West Texas.

Sprinks said:
CW,

You can find help on the Nz and any other VBA function in VBA Help. Press
F11 to load the VBA Editor, and select Help.

Nz avoids the error caused by attempting to do arithmetic with a Null value
by converting it to a value of zero (or any other value that you specify in
its optional parameter). So, to add two values, either of which might be
Null, set the ControlSource of your calculated control to:

=Nz([FirstValue]) + Nz([SecondValue])

Also, I hope that when you say, "subtotals and totals in other *fields*"
that what you really mean is "subtotals and totals in other *form controls*".
As is often discussed on this forum, calculated values should, with very
rare exception, should be calculated on-the-fly in a query upon which the
form is based or in a form control, rather than storing it in an underlying
field using code.

To do so is slower, wastes disk space, and risks inaccurate data should a
user change a value involved in the calculation outside the context of your
form.

Hope that helps.
Sprinks


CW said:
Thanks for the suggestion but Nz is "not found" anywhere in Help !!!!
Click to expand...
Click to expand...
 
Klatuu,

Appropriate, although being a native Chicagoan I prefer the more restrictive
"when the Cubs win the World Series".

Sprinks


Klatuu said:
My rule on using stored calculations is that it is only allowed when it is
raining in West Texas.

Sprinks said:
CW,

You can find help on the Nz and any other VBA function in VBA Help. Press
F11 to load the VBA Editor, and select Help.

Nz avoids the error caused by attempting to do arithmetic with a Null value
by converting it to a value of zero (or any other value that you specify in
its optional parameter). So, to add two values, either of which might be
Null, set the ControlSource of your calculated control to:

=Nz([FirstValue]) + Nz([SecondValue])

Also, I hope that when you say, "subtotals and totals in other *fields*"
that what you really mean is "subtotals and totals in other *form controls*".
As is often discussed on this forum, calculated values should, with very
rare exception, should be calculated on-the-fly in a query upon which the
form is based or in a form control, rather than storing it in an underlying
field using code.

To do so is slower, wastes disk space, and risks inaccurate data should a
user change a value involved in the calculation outside the context of your
form.

Hope that helps.
Sprinks


CW said:
Thanks for the suggestion but Nz is "not found" anywhere in Help !!!!

:

Hi

You could use the Nz function.

Press F1 and search on Nz

--
Wayne
Manchester, England.
Enjoy whatever it is you do


:

I have a number of fields that are added up to give sub-totals and totals in
other fields.
It appears that if any of those individual fields is blank, the expressions
in the SubTotal and Total fields (to add all those values) will not work.
It looks as though I must set the default to 0 in each of the fields, for
the calculation to work.
This is a bit of a pain as there are lots of them and it will be quite a bit
of work to go through them all adding a default value, so I thought I'd check
before starting on this exciting little (not so little!) task.
Thanks
CW
Click to expand...
Click to expand...
Click to expand...
 
LOL
The two events happen about as often.

Sprinks said:
Klatuu,

Appropriate, although being a native Chicagoan I prefer the more restrictive
"when the Cubs win the World Series".

Sprinks


Klatuu said:
My rule on using stored calculations is that it is only allowed when it is
raining in West Texas.

Sprinks said:
CW,

You can find help on the Nz and any other VBA function in VBA Help. Press
F11 to load the VBA Editor, and select Help.

Nz avoids the error caused by attempting to do arithmetic with a Null value
by converting it to a value of zero (or any other value that you specify in
its optional parameter). So, to add two values, either of which might be
Null, set the ControlSource of your calculated control to:

=Nz([FirstValue]) + Nz([SecondValue])

Also, I hope that when you say, "subtotals and totals in other *fields*"
that what you really mean is "subtotals and totals in other *form controls*".
As is often discussed on this forum, calculated values should, with very
rare exception, should be calculated on-the-fly in a query upon which the
form is based or in a form control, rather than storing it in an underlying
field using code.

To do so is slower, wastes disk space, and risks inaccurate data should a
user change a value involved in the calculation outside the context of your
form.

Hope that helps.
Sprinks


:

Thanks for the suggestion but Nz is "not found" anywhere in Help !!!!

:

Hi

You could use the Nz function.

Press F1 and search on Nz

--
Wayne
Manchester, England.
Enjoy whatever it is you do


:

I have a number of fields that are added up to give sub-totals and totals in
other fields.
It appears that if any of those individual fields is blank, the expressions
in the SubTotal and Total fields (to add all those values) will not work.
It looks as though I must set the default to 0 in each of the fields, for
the calculation to work.
This is a bit of a pain as there are lots of them and it will be quite a bit
of work to go through them all adding a default value, so I thought I'd check
before starting on this exciting little (not so little!) task.
Thanks
CW
Click to expand...
Click to expand...
Click to expand...
 
Many thanks to both for your clear advice - it looks as though Nz will do
just what I need.
Yes, when I referred to "fields" I should properly have said "form controls".
I note your words of caution about stored calculations but as these are
really only very simple I was hoping it would be OK to do it this way - not
least because I am in England and it is usually raining........
For example I have several lines for the various cost elements of a service
being provided, which I add up into a Cost Subtotal field (oops sorry,
control!) via an expression with a number of +++++s. Then I apply a profit
factor of (say) 20% which I have done by means of an expression in a Profit
control that looks at CostSubtotal and multiplies it by 20 and divides by
100. Then the final control, Charge, is the sum of CostSubtotal and Profit.
No user will ever change anything outside those controls that would impact
any of those values. Therefore, is the storing of these "calculated fields"
OK?
Thanks again
CW


Klatuu said:
LOL
The two events happen about as often.

Sprinks said:
Klatuu,

Appropriate, although being a native Chicagoan I prefer the more restrictive
"when the Cubs win the World Series".

Sprinks


Klatuu said:
My rule on using stored calculations is that it is only allowed when it is
raining in West Texas.

:

CW,

You can find help on the Nz and any other VBA function in VBA Help. Press
F11 to load the VBA Editor, and select Help.

Nz avoids the error caused by attempting to do arithmetic with a Null value
by converting it to a value of zero (or any other value that you specify in
its optional parameter). So, to add two values, either of which might be
Null, set the ControlSource of your calculated control to:

=Nz([FirstValue]) + Nz([SecondValue])

Also, I hope that when you say, "subtotals and totals in other *fields*"
that what you really mean is "subtotals and totals in other *form controls*".
As is often discussed on this forum, calculated values should, with very
rare exception, should be calculated on-the-fly in a query upon which the
form is based or in a form control, rather than storing it in an underlying
field using code.

To do so is slower, wastes disk space, and risks inaccurate data should a
user change a value involved in the calculation outside the context of your
form.

Hope that helps.
Sprinks


:

Thanks for the suggestion but Nz is "not found" anywhere in Help !!!!

:

Hi

You could use the Nz function.

Press F1 and search on Nz

--
Wayne
Manchester, England.
Enjoy whatever it is you do


:

I have a number of fields that are added up to give sub-totals and totals in
other fields.
It appears that if any of those individual fields is blank, the expressions
in the SubTotal and Total fields (to add all those values) will not work.
It looks as though I must set the default to 0 in each of the fields, for
the calculation to work.
This is a bit of a pain as there are lots of them and it will be quite a bit
of work to go through them all adding a default value, so I thought I'd check
before starting on this exciting little (not so little!) task.
Thanks
CW
Click to expand...
Click to expand...
Click to expand...
 
Only when it stops raining in England :)
There really is no need to store a calculated value.

CW said:
Many thanks to both for your clear advice - it looks as though Nz will do
just what I need.
Yes, when I referred to "fields" I should properly have said "form controls".
I note your words of caution about stored calculations but as these are
really only very simple I was hoping it would be OK to do it this way - not
least because I am in England and it is usually raining........
For example I have several lines for the various cost elements of a service
being provided, which I add up into a Cost Subtotal field (oops sorry,
control!) via an expression with a number of +++++s. Then I apply a profit
factor of (say) 20% which I have done by means of an expression in a Profit
control that looks at CostSubtotal and multiplies it by 20 and divides by
100. Then the final control, Charge, is the sum of CostSubtotal and Profit.
No user will ever change anything outside those controls that would impact
any of those values. Therefore, is the storing of these "calculated fields"
OK?
Thanks again
CW


Klatuu said:
LOL
The two events happen about as often.

Sprinks said:
Klatuu,

Appropriate, although being a native Chicagoan I prefer the more restrictive
"when the Cubs win the World Series".

Sprinks


:

My rule on using stored calculations is that it is only allowed when it is
raining in West Texas.

:

CW,

You can find help on the Nz and any other VBA function in VBA Help. Press
F11 to load the VBA Editor, and select Help.

Nz avoids the error caused by attempting to do arithmetic with a Null value
by converting it to a value of zero (or any other value that you specify in
its optional parameter). So, to add two values, either of which might be
Null, set the ControlSource of your calculated control to:

=Nz([FirstValue]) + Nz([SecondValue])

Also, I hope that when you say, "subtotals and totals in other *fields*"
that what you really mean is "subtotals and totals in other *form controls*".
As is often discussed on this forum, calculated values should, with very
rare exception, should be calculated on-the-fly in a query upon which the
form is based or in a form control, rather than storing it in an underlying
field using code.

To do so is slower, wastes disk space, and risks inaccurate data should a
user change a value involved in the calculation outside the context of your
form.

Hope that helps.
Sprinks


:

Thanks for the suggestion but Nz is "not found" anywhere in Help !!!!

:

Hi

You could use the Nz function.

Press F1 and search on Nz

--
Wayne
Manchester, England.
Enjoy whatever it is you do


:

I have a number of fields that are added up to give sub-totals and totals in
other fields.
It appears that if any of those individual fields is blank, the expressions
in the SubTotal and Total fields (to add all those values) will not work.
It looks as though I must set the default to 0 in each of the fields, for
the calculation to work.
This is a bit of a pain as there are lots of them and it will be quite a bit
of work to go through them all adding a default value, so I thought I'd check
before starting on this exciting little (not so little!) task.
Thanks
CW
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Default Value on Form Help 1
Total Calculation 3
Calculated Field in Form 6
Using value from calculated field 7
Adding the values of multiple fields 1
Values calculated in a Form entered in the Table? 6
calculated field in new line sometimes gives an error 1
Summing Calculated Fields 11

Back
Top