CALCULATED FIELDS

[Calvin]

I have a field (txtMaterialTotal) that calculates the sum
of many other fields. These other fields can not have a
zero value(so I can accurately calculate the average later
in a report). txtMaterialTotal will only show the sum if
all the fields have values in them. If one cell does not
have a value, its blank because I cant have zeros, then
the sum function does not work.

How can I have a txt box that calculates the sum even when
one of the fields in the calculation is blank?

Thank you -Calvin

 

[Hallgeir]

I have a field (txtMaterialTotal) that calculates the sum
of many other fields. These other fields can not have a
zero value(so I can accurately calculate the average later
in a report). txtMaterialTotal will only show the sum if
all the fields have values in them. If one cell does not
have a value, its blank because I cant have zeros, then
the sum function does not work.

How can I have a txt box that calculates the sum even when
one of the fields in the calculation is blank?

Thank you -Calvin

I had a simular problem when I was building a query. I used this function to
convert empty values to zero: Val(Nz([txtFieldname];0))
I don't know but maybe this also can give you help or at least a hint.

mvh
Hallgeir

 

[ken h]

Note: The 'Nz' function does not appear in the help index (At least not in mine). The only way I know to get to the help on this function is to go through the expression builder wizard...

 

[Guest]

That will still return a zero value. When i run my
report, and i calculate averages, i can not have zeros
because that will skew the results. Is there an average
formula that does not account for zero values

My other option is using a count vaiable that counts the
number of fields with values greater then zero. Then uses
that number do divide by the sum.
The problem with this is I have a lot of fields!!!

-----Original Message-----

I have a field (txtMaterialTotal) that calculates the sum
of many other fields. These other fields can not have a
zero value(so I can accurately calculate the average later
in a report). txtMaterialTotal will only show the sum if
all the fields have values in them. If one cell does not
have a value, its blank because I cant have zeros, then
the sum function does not work.

How can I have a txt box that calculates the sum even when
one of the fields in the calculation is blank?

Thank you -Calvin

I had a simular problem when I was building a query. I used this function to
convert empty values to zero: Val(Nz([txtFieldname];0))
I don't know but maybe this also can give you help or at least a hint.

mvh
Hallgeir


.

 

[ken h]

Is this a form or a report?

 

[Van T. Dinh]

It should be in Access VB(A) Help, not the normal Access Help.

You can activate the Access VB(A) Help from the VBE - Visual Basic
(integrated development) Environment, i.e. the VBA code window.

--
HTH
Van T. Dinh
MVP (Access)

Note: The 'Nz' function does not appear in the help index (At least not in

mine). The only way I know to get to the help on this function is to go
through the expression builder wizard...

 

[Guest]

The values are calculated in a report.
Im not sure about VB(A), What does the A stand for?
I also am not sure about the VBE

 

[ken h]

I'll be darn - Funny how when you find a work around you stop looking for the right answer...Thanks