automatic zero if answer is null

[Pritesh_karma]

In the expression builder, Im calculating a %.
=Round(Sum([5_2_1e_ii])/Sum([5_2_1e_i])*100,2)

but when i get Null values, the output is #NUM, I need it to be zero.
How do i do this?

 

[Guest]

Hi Pritesh

I Take it that [5_2_1e_ii]) ([5_2_1e_i]) are number fields that may or may
not contain data (a number). if so then use

=Round(Sum(Nz([5_2_1e_ii]))/Sum(Nz([5_2_1e_i]))*100,2)

For more info go to help and search on Null=Zero or Nz

Hope this helps

 

[Pieter Wijnen]

the answer is probably
=IIF(Nz(Sum([5_2_1e_i]);0)=0;0;Round(Sum([5_2_1e_ii])/Sum([5_2_1e_i])*100,2))

I use a SafeDiv Function myself:

Public Function SaveDiv(ByVal T As Variant, ByVal N As Variant) AS Double
On Error Resume Next
N= Nz(N,0)
If N=0 Then Exit Function
T=Nz(T,0)
SaveDiv = T/N
End Function

HTH

Pieter

Hi Pritesh

I Take it that [5_2_1e_ii]) ([5_2_1e_i]) are number fields that may or may
not contain data (a number). if so then use

=Round(Sum(Nz([5_2_1e_ii]))/Sum(Nz([5_2_1e_i]))*100,2)

For more info go to help and search on Null=Zero or Nz

Hope this helps

--
Wayne
Manchester, England.

In the expression builder, Im calculating a %.
=Round(Sum([5_2_1e_ii])/Sum([5_2_1e_i])*100,2)

but when i get Null values, the output is #NUM, I need it to be zero.
How do i do this?

--

 

[Pritesh_karma]

I think i asked the question incorecctly...
=IIf(IsError(Sum([5_2_1e_iii])/Sum([5_2_1e_i]))*100),0,Round(Sum([5_2_1e_iii])/Sum([5_2_1e_i])*100,2))
thats what im using...
But from my db i get values that make for a 0/0 which is undefined.

And when i get this, the output is #NUM!

I need it to convert it to 0.

The expression im evaluating is, Sum([5_2_1e_iii])/Sum([5_2_1e_i]))*100
where both 5_2_1e_ii and 5_2_1e_i are numbers.
Please help...
thanks.

Hi Pritesh

I Take it that [5_2_1e_ii]) ([5_2_1e_i]) are number fields that may or may
not contain data (a number). if so then use

=Round(Sum(Nz([5_2_1e_ii]))/Sum(Nz([5_2_1e_i]))*100,2)

For more info go to help and search on Null=Zero or Nz

Hope this helps

--
Wayne
Manchester, England.

In the expression builder, Im calculating a %.
=Round(Sum([5_2_1e_ii])/Sum([5_2_1e_i])*100,2)

but when i get Null values, the output is #NUM, I need it to be zero.
How do i do this?

 

[Pritesh_karma]

thanks.

i dunno how to do that...
the expression im using is in the expression builder.
is there no way to do it there?
Im not very clued up on writing functions.

the answer is probably
=IIF(Nz(Sum([5_2_1e_i]);0)=0;0;Round(Sum([5_2_1e_ii])/Sum([5_2_1e_i])*100,2))

I use a SafeDiv Function myself:

Public Function SaveDiv(ByVal T As Variant, ByVal N As Variant) AS Double
On Error Resume Next
N= Nz(N,0)
If N=0 Then Exit Function
T=Nz(T,0)
SaveDiv = T/N
End Function

HTH

Pieter

Hi Pritesh

I Take it that [5_2_1e_ii]) ([5_2_1e_i]) are number fields that may or may
not contain data (a number). if so then use

=Round(Sum(Nz([5_2_1e_ii]))/Sum(Nz([5_2_1e_i]))*100,2)

For more info go to help and search on Null=Zero or Nz

Hope this helps

--
Wayne
Manchester, England.

In the expression builder, Im calculating a %.
=Round(Sum([5_2_1e_ii])/Sum([5_2_1e_i])*100,2)

but when i get Null values, the output is #NUM, I need it to be zero.
How do i do this?

--
----------------------------------------
I am using the free version of SPAMfighter for private users.
It has removed 4182 spam emails to date.
Paying users do not have this message in their emails.
Get the free SPAMfighter here: http://www.spamfighter.com/len

 

[Pritesh_karma]

Thanks guys...
I was a little silly...
I found a way to do it...

All i do is check if the number being divided by(5_2_1e_i is zero, if
it is. i make the answer 0.

Thanks for all your help tho.

thanks.

i dunno how to do that...
the expression im using is in the expression builder.
is there no way to do it there?
Im not very clued up on writing functions.

the answer is probably
=IIF(Nz(Sum([5_2_1e_i]);0)=0;0;Round(Sum([5_2_1e_ii])/Sum([5_2_1e_i])*100,2))

I use a SafeDiv Function myself:

Public Function SaveDiv(ByVal T As Variant, ByVal N As Variant) AS Double
On Error Resume Next
N= Nz(N,0)
If N=0 Then Exit Function
T=Nz(T,0)
SaveDiv = T/N
End Function

HTH

Pieter

Hi Pritesh

I Take it that [5_2_1e_ii]) ([5_2_1e_i]) are number fields that may or may
not contain data (a number). if so then use

=Round(Sum(Nz([5_2_1e_ii]))/Sum(Nz([5_2_1e_i]))*100,2)

For more info go to help and search on Null=Zero or Nz

Hope this helps

--
Wayne
Manchester, England.



:

In the expression builder, Im calculating a %.
=Round(Sum([5_2_1e_ii])/Sum([5_2_1e_i])*100,2)

but when i get Null values, the output is #NUM, I need it to be zero.
How do i do this?

--
----------------------------------------
I am using the free version of SPAMfighter for private users.
It has removed 4182 spam emails to date.
Paying users do not have this message in their emails.
Get the free SPAMfighter here: http://www.spamfighter.com/len

 

[Pieter Wijnen]

Make a new module , paste the code & save

Then use
=Round(SafeDiv(Sum([5_2_1e_ii]),Sum([5_2_1e_i]))*100,2)
as the controlsource

Pieter

thanks.

i dunno how to do that...
the expression im using is in the expression builder.
is there no way to do it there?
Im not very clued up on writing functions.

the answer is probably
=IIF(Nz(Sum([5_2_1e_i]);0)=0;0;Round(Sum([5_2_1e_ii])/Sum([5_2_1e_i])*100,2))

I use a SafeDiv Function myself:

Public Function SaveDiv(ByVal T As Variant, ByVal N As Variant) AS Double
On Error Resume Next
N= Nz(N,0)
If N=0 Then Exit Function
T=Nz(T,0)
SaveDiv = T/N
End Function

HTH

Pieter

Hi Pritesh

I Take it that [5_2_1e_ii]) ([5_2_1e_i]) are number fields that may or
may
not contain data (a number). if so then use

=Round(Sum(Nz([5_2_1e_ii]))/Sum(Nz([5_2_1e_i]))*100,2)

For more info go to help and search on Null=Zero or Nz

Hope this helps

--
Wayne
Manchester, England.



:

In the expression builder, Im calculating a %.
=Round(Sum([5_2_1e_ii])/Sum([5_2_1e_i])*100,2)

but when i get Null values, the output is #NUM, I need it to be zero.
How do i do this?

--
----------------------------------------
I am using the free version of SPAMfighter for private users.
It has removed 4182 spam emails to date.
Paying users do not have this message in their emails.
Get the free SPAMfighter here: http://www.spamfighter.com/len

--

 

[Guest]

Can you post

The formula you are using
The answer you are getting
The answer you want to get.

It would help if you could give an example of what the fields [5_2_1e_iii]
and [5_2_1e_i] contain.


--
Wayne
Manchester, England.

I think i asked the question incorecctly...
=IIf(IsError(Sum([5_2_1e_iii])/Sum([5_2_1e_i]))*100),0,Round(Sum([5_2_1e_iii])/Sum([5_2_1e_i])*100,2))
thats what im using...
But from my db i get values that make for a 0/0 which is undefined.

And when i get this, the output is #NUM!

I need it to convert it to 0.

The expression im evaluating is, Sum([5_2_1e_iii])/Sum([5_2_1e_i]))*100
where both 5_2_1e_ii and 5_2_1e_i are numbers.
Please help...
thanks.

Hi Pritesh

I Take it that [5_2_1e_ii]) ([5_2_1e_i]) are number fields that may or may
not contain data (a number). if so then use

=Round(Sum(Nz([5_2_1e_ii]))/Sum(Nz([5_2_1e_i]))*100,2)

For more info go to help and search on Null=Zero or Nz

Hope this helps

--
Wayne
Manchester, England.

In the expression builder, Im calculating a %.
=Round(Sum([5_2_1e_ii])/Sum([5_2_1e_i])*100,2)

but when i get Null values, the output is #NUM, I need it to be zero.
How do i do this?