help with dividing formula

[Guest]

Hello I want to divide two rows of text boxes together. These text boxes have
time in them and the result I want is a %. The formula I was going to use is
=[Text1]/[Text2]&"% But it will not work because some of the text boxes will
be blank and this will give me an error. I had somone help me witha module
for something simular it went like this:
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
This was so I can add a row of text together and if one of the boxes were
empty it would still add them and give me a total! He said I can use it for
this also but as an IIF statement but I cant figure it out? The boxes I need
to divide by are: Field41,Field44,Field45,Field46,Field47,Field48,Field49
divided by Text77,Text79,Text80,Text82,Text84,Text86,Text87
Thanks for the help this is my first database and im new to access!

 

[Pieter Wijnen]

set the control's format property to "Percent"

Pieter

 

[Guest]

That doesnt answer my question? I need to divide two rows together and want
to utilize the module. just like explained in my post?Thanks for the
sugestion though!!

set the control's format property to "Percent"

Pieter

Hello I want to divide two rows of text boxes together. These text boxes
have
time in them and the result I want is a %. The formula I was going to use
is
=[Text1]/[Text2]&"% But it will not work because some of the text boxes
will
be blank and this will give me an error. I had somone help me witha module
for something simular it went like this:
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
This was so I can add a row of text together and if one of the boxes were
empty it would still add them and give me a total! He said I can use it
for
this also but as an IIF statement but I cant figure it out? The boxes I
need
to divide by are: Field41,Field44,Field45,Field46,Field47,Field48,Field49
divided by Text77,Text79,Text80,Text82,Text84,Text86,Text87
Thanks for the help this is my first database and im new to access!

 

[John Vinson]

Hello I want to divide two rows of text boxes together. These text boxes have
time in them and the result I want is a %. The formula I was going to use is
=[Text1]/[Text2]&"% But it will not work because some of the text boxes will
be blank and this will give me an error. I had somone help me witha module
for something simular it went like this:
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
This was so I can add a row of text together and if one of the boxes were
empty it would still add them and give me a total! He said I can use it for
this also but as an IIF statement but I cant figure it out? The boxes I need
to divide by are: Field41,Field44,Field45,Field46,Field47,Field48,Field49
divided by Text77,Text79,Text80,Text82,Text84,Text86,Text87
Thanks for the help this is my first database and im new to access!

YOu do not need the NNZ function. The builtin NZ() function will do it
for you.

I'm not sure what your expression is though. Do you want to divide the
sum of the seven (very badly named, you may want to change the default
fieldnames to something meaningful) Field values by the sum of the
seven Text values? Or do you want seven separate results?

If the former:

(NZ(Field41) + NZ(Field44) + NZ(Field45) + NZ(Field46) + NZ(Field47)
+ NZ(Field48) + NZ(Field49)) / (NZ(Text77) + NZ(Text79) + NZ(Text80) +
NZ(Text82) + NZ(Text84) + NZ(Text86) + NZ(Text87))

If the latter use expressions like

IIF(NZ([Text77]) = 0, NULL, NZ([Field41]) / [Text77])

This will prevent Access from attempting to divide by zero if the
denominator is NULL or zero, otherwise return the quotient. You can
set the Format property of the textbox in which this value is
displayed to Percent to take a quotient like 0.813 and display it as
81.3% (your expression would give an erroneous result of 0.813%).

John W. Vinson[MVP]

 

[Guest]

Yes they are very badly named thats so funny because my brother was just
telling me that! Anyway I want to divide the 7 boxes by the other 7 boxes.
but some of the boxes will not have anything in them. what do I do? and will
this prevent an error? Thanks!!!

Hello I want to divide two rows of text boxes together. These text boxes have
time in them and the result I want is a %. The formula I was going to use is
=[Text1]/[Text2]&"% But it will not work because some of the text boxes will
be blank and this will give me an error. I had somone help me witha module
for something simular it went like this:
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
This was so I can add a row of text together and if one of the boxes were
empty it would still add them and give me a total! He said I can use it for
this also but as an IIF statement but I cant figure it out? The boxes I need
to divide by are: Field41,Field44,Field45,Field46,Field47,Field48,Field49
divided by Text77,Text79,Text80,Text82,Text84,Text86,Text87
Thanks for the help this is my first database and im new to access!

YOu do not need the NNZ function. The builtin NZ() function will do it
for you.

I'm not sure what your expression is though. Do you want to divide the
sum of the seven (very badly named, you may want to change the default
fieldnames to something meaningful) Field values by the sum of the
seven Text values? Or do you want seven separate results?

If the former:

(NZ(Field41) + NZ(Field44) + NZ(Field45) + NZ(Field46) + NZ(Field47)
+ NZ(Field48) + NZ(Field49)) / (NZ(Text77) + NZ(Text79) + NZ(Text80) +
NZ(Text82) + NZ(Text84) + NZ(Text86) + NZ(Text87))

If the latter use expressions like

IIF(NZ([Text77]) = 0, NULL, NZ([Field41]) / [Text77])

This will prevent Access from attempting to divide by zero if the
denominator is NULL or zero, otherwise return the quotient. You can
set the Format property of the textbox in which this value is
displayed to Percent to take a quotient like 0.813 and display it as
81.3% (your expression would give an erroneous result of 0.813%).

John W. Vinson[MVP]

 

[Guest]

John woud I use this:

=(Nnz([Field41])+Nnz([Field44])+Nnz([Field45])+Nnz([Field46])+Nnz([Field47])+Nnz([Field48])+Nnz([Field49])+Nnz([Field48])+Nnz([Field49]))/(Nnz([Text77])+Nnz([Text79])+Nnz([Text80])+Nnz([Text82])+Nnz([Text84])+Nnz([Text86])+Nnz([Text87]))

But it gives me a #Num! error

Yes they are very badly named thats so funny because my brother was just
telling me that! Anyway I want to divide the 7 boxes by the other 7 boxes.
but some of the boxes will not have anything in them. what do I do? and will
this prevent an error? Thanks!!!

Hello I want to divide two rows of text boxes together. These text boxes have
time in them and the result I want is a %. The formula I was going to use is
=[Text1]/[Text2]&"% But it will not work because some of the text boxes will
be blank and this will give me an error. I had somone help me witha module
for something simular it went like this:
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
This was so I can add a row of text together and if one of the boxes were
empty it would still add them and give me a total! He said I can use it for
this also but as an IIF statement but I cant figure it out? The boxes I need
to divide by are: Field41,Field44,Field45,Field46,Field47,Field48,Field49
divided by Text77,Text79,Text80,Text82,Text84,Text86,Text87
Thanks for the help this is my first database and im new to access!

YOu do not need the NNZ function. The builtin NZ() function will do it
for you.

I'm not sure what your expression is though. Do you want to divide the
sum of the seven (very badly named, you may want to change the default
fieldnames to something meaningful) Field values by the sum of the
seven Text values? Or do you want seven separate results?

If the former:

(NZ(Field41) + NZ(Field44) + NZ(Field45) + NZ(Field46) + NZ(Field47)
+ NZ(Field48) + NZ(Field49)) / (NZ(Text77) + NZ(Text79) + NZ(Text80) +
NZ(Text82) + NZ(Text84) + NZ(Text86) + NZ(Text87))

If the latter use expressions like

IIF(NZ([Text77]) = 0, NULL, NZ([Field41]) / [Text77])

This will prevent Access from attempting to divide by zero if the
denominator is NULL or zero, otherwise return the quotient. You can
set the Format property of the textbox in which this value is
displayed to Percent to take a quotient like 0.813 and display it as
81.3% (your expression would give an erroneous result of 0.813%).

John W. Vinson[MVP]

 

[John Vinson]

John woud I use this:

=(Nnz([Field41])+Nnz([Field44])+Nnz([Field45])+Nnz([Field46])+Nnz([Field47])+Nnz([Field48])+Nnz([Field49])+Nnz([Field48])+Nnz([Field49]))/(Nnz([Text77])+Nnz([Text79])+Nnz([Text80])+Nnz([Text82])+Nnz([Text84])+Nnz([Text86])+Nnz([Text87]))

But it gives me a #Num! error

Did you read my message?


John W. Vinson[MVP]

 

[Guest]

Yes I read your message and it also gave the same error!!

John woud I use this:

=(Nnz([Field41])+Nnz([Field44])+Nnz([Field45])+Nnz([Field46])+Nnz([Field47])+Nnz([Field48])+Nnz([Field49])+Nnz([Field48])+Nnz([Field49]))/(Nnz([Text77])+Nnz([Text79])+Nnz([Text80])+Nnz([Text82])+Nnz([Text84])+Nnz([Text86])+Nnz([Text87]))

But it gives me a #Num! error

Did you read my message?


John W. Vinson[MVP]

 

[John Vinson]

Yes I read your message and it also gave the same error!!

Please post the actual expression you're using. Do any of these fields
or controls contain non-numeric data (such as the % character)? The
Num#! message means that you're trying to perform a mathematical
operation on non-numeric data.

John W. Vinson[MVP]