Formula for a sum

[Guest]

Hello, I have two questions? I have a row of text boxs that contain numbers
(when entered). I am using
=(([GrossProduct1]+[GrossProduct2]+[GrossProduct3]+[GrossProduct4]+[GrossProduct5]+[GrossProduct6]+[GrossProduct7]))
and it works only if I enter something in those boxs like 22,000 or zero ect.
It doesnt work untill I enter 0 or somthing. What is another formula I can
use that would work if i only enter a number in a box and have the rest
blank? My second question is I have two rows on called total hours worked
which is a time and the other row is called delay time which is a time also.
I want to divide the totals of each row together but some rows will not have
a time in it so I dont want to get a error because a time isnt entered in one
or the other rows. The text boxes for delay time are
Field41,Field44,Field45,Field46,Field47,Feild48,Field49 and the text boxes
for hours worked is called Text77,Text79,Text80,Text82,Text84,Text86,Text87.
Thanks!!!

 

[Guest]

The problem is that blank values are not numeric so Access can't add them. I
have created a function that you can use to check the values before trying to
add them:
1) Create a new module
2) Paste in the following:

Public Function Nnz(TestValue As Variant) As Single
'*** Converts non-numeric values to zero ***
If Not (IsNumeric(TestValue)) Then
Nnz = 0
Else
Nnz = TestValue
End If
End Function

3) Save the module (You can give it any name you like)

in your formula use:

=((Nnz([GrossProduct1])+Nnz([GrossProduct2])... etc

You should be able to use a similar approach for your times although you
will get errors if you try and divide by zero. In this case you could use an
IIf statement to check for this, e.g.

IIf(nnz(myValue2>0),myvalue1/myvalue2,0)

Suggest you name your text boxes to make working with them easier, e.g
'txtTime1'

 

[Guest]

Thanks for your help!!!!

The problem is that blank values are not numeric so Access can't add them. I
have created a function that you can use to check the values before trying to
add them:
1) Create a new module
2) Paste in the following:

Public Function Nnz(TestValue As Variant) As Single
'*** Converts non-numeric values to zero ***
If Not (IsNumeric(TestValue)) Then
Nnz = 0
Else
Nnz = TestValue
End If
End Function

3) Save the module (You can give it any name you like)

in your formula use:

=((Nnz([GrossProduct1])+Nnz([GrossProduct2])... etc

You should be able to use a similar approach for your times although you
will get errors if you try and divide by zero. In this case you could use an
IIf statement to check for this, e.g.

IIf(nnz(myValue2>0),myvalue1/myvalue2,0)

Suggest you name your text boxes to make working with them easier, e.g
'txtTime1'

--
Peter Schmidt
Ross-on-Wye, UK

Hello, I have two questions? I have a row of text boxs that contain numbers
(when entered). I am using
=(([GrossProduct1]+[GrossProduct2]+[GrossProduct3]+[GrossProduct4]+[GrossProduct5]+[GrossProduct6]+[GrossProduct7]))
and it works only if I enter something in those boxs like 22,000 or zero ect.
It doesnt work untill I enter 0 or somthing. What is another formula I can
use that would work if i only enter a number in a box and have the rest
blank? My second question is I have two rows on called total hours worked
which is a time and the other row is called delay time which is a time also.
I want to divide the totals of each row together but some rows will not have
a time in it so I dont want to get a error because a time isnt entered in one
or the other rows. The text boxes for delay time are
Field41,Field44,Field45,Field46,Field47,Feild48,Field49 and the text boxes
for hours worked is called Text77,Text79,Text80,Text82,Text84,Text86,Text87.
Thanks!!!

 

[Joseph Meehan]

Hello, I have two questions? I have a row of text boxs that contain
numbers (when entered). I am using
=(([GrossProduct1]+[GrossProduct2]+[GrossProduct3]+[GrossProduct4]+[GrossProduct5]+[GrossProduct6]+[GrossProduct7]))
and it works only if I enter something in those boxs like 22,000 or
zero ect. It doesnt work untill I enter 0 or somthing. What is
another formula I can use that would work if i only enter a number in
a box and have the rest blank? My second question is I have two rows
on called total hours worked which is a time and the other row is
called delay time which is a time also. I want to divide the totals
of each row together but some rows will not have a time in it so I
dont want to get a error because a time isnt entered in one or the
other rows. The text boxes for delay time are
Field41,Field44,Field45,Field46,Field47,Feild48,Field49 and the text
boxes for hours worked is called
Text77,Text79,Text80,Text82,Text84,Text86,Text87. Thanks!!!

Pete certainly picked up on your problem as asked, but I would suggest
that you have another problem.

It would appear likely that you need to consider normalizing your
table(s). Anytime I see 87 tables, especially when they are named
sequentially, it makes me think that something is not right with the table
design and continuing with this design will only cause problems.

 

[Guest]

Pete wrote I need to use IIf(nnz(myValue2>0),myvalue1/myvalue2,0)
for the second half of my question but after thinking about it I have no
clue how to write it? Im trying to divide one row of text boxes by the other
row. But some of the boxes might not have "time" in it. This will give me an
error. Can you explain how the formula would go in a little more detail
because in new at access and this is my first database. I appreciate yours
and everbodys help its been great!! My first question with just totaling
worked great!