#Num! Error

[Reese]

Here is the expression I'm using. If there are no returns in the field
[Warranty Income] then I'm getting the #Num! error. If there are no
warranties in the range I'd like to have "0" returned instead of #Num!.
=Abs(Sum([Warranty Income])/Sum([Warranty Income]>=1))

 

[Duane Hookom]

You might want to check the answer to the question posted immediately prior
to yours. If you don't find the answer, post back.

 

[Reese]

That doesn't address my question. My problem is that if there are no records
to count in the fields in question I get a #Num! showing up on my report.
I'd rather have that return a "0" instead.

You might want to check the answer to the question posted immediately prior
to yours. If you don't find the answer, post back.

--
Duane Hookom
Microsoft Access MVP

Here is the expression I'm using. If there are no returns in the field
[Warranty Income] then I'm getting the #Num! error. If there are no
warranties in the range I'd like to have "0" returned instead of #Num!.
=Abs(Sum([Warranty Income])/Sum([Warranty Income]>=1))

 

[KARL DEWEY]

Do not understand what you are trying to do --
Sum([Warranty Income]>=1)
Is this supose to only sum those amounts that are equal or larger than one?
If that is what you want then use --
Sum(IIF(Nz([Warranty Income], 0) >=1, [Warranty Income], 0))

Apply Nz like this Nz([Warranty Income], 0) to change any nulls to zero.

--
KARL DEWEY
Build a little - Test a little

That doesn't address my question. My problem is that if there are no records
to count in the fields in question I get a #Num! showing up on my report.
I'd rather have that return a "0" instead.

You might want to check the answer to the question posted immediately prior
to yours. If you don't find the answer, post back.

--
Duane Hookom
Microsoft Access MVP

Here is the expression I'm using. If there are no returns in the field
[Warranty Income] then I'm getting the #Num! error. If there are no
warranties in the range I'd like to have "0" returned instead of #Num!.
=Abs(Sum([Warranty Income])/Sum([Warranty Income]>=1))

 

[Duane Hookom]

I can't figure if you are getting divide by zero issues or nulls. If it is
divide by 0 then use
=IIf( {some expression}=0,0, {another expression}/{some expression})
If it is a problem with Nulls, use Nz({another expression},{substitute
expression})

Duane Hookom
Microsoft Access MVP

That doesn't address my question. My problem is that if there are no records
to count in the fields in question I get a #Num! showing up on my report.
I'd rather have that return a "0" instead.

You might want to check the answer to the question posted immediately prior
to yours. If you don't find the answer, post back.

--
Duane Hookom
Microsoft Access MVP

Here is the expression I'm using. If there are no returns in the field
[Warranty Income] then I'm getting the #Num! error. If there are no
warranties in the range I'd like to have "0" returned instead of #Num!.
=Abs(Sum([Warranty Income])/Sum([Warranty Income]>=1))

 

[Reese]

I'm trying to add the total income from the [Warranty Income] field, then to
divide that number by the number of instances that the field [Warranty
Income] had a value of greater than 1. ex. total income 1644 divided by 4
warranties sold to give a value of 411. However, if in any of the subgroups
that the report is running has no warranties sold, which would mean no
[Warranty Income] with a value greater than 1, the return I get is #Num!.
The expression I'm using is:
=Abs(Sum([Warranty Income]))/Abs(Sum([Warranty Income]>=1))

I can't figure if you are getting divide by zero issues or nulls. If it is
divide by 0 then use
=IIf( {some expression}=0,0, {another expression}/{some expression})
If it is a problem with Nulls, use Nz({another expression},{substitute
expression})

Duane Hookom
Microsoft Access MVP

That doesn't address my question. My problem is that if there are no records
to count in the fields in question I get a #Num! showing up on my report.
I'd rather have that return a "0" instead.

You might want to check the answer to the question posted immediately prior
to yours. If you don't find the answer, post back.

--
Duane Hookom
Microsoft Access MVP


:

Here is the expression I'm using. If there are no returns in the field
[Warranty Income] then I'm getting the #Num! error. If there are no
warranties in the range I'd like to have "0" returned instead of #Num!.
=Abs(Sum([Warranty Income])/Sum([Warranty Income]>=1))

 

[Duane Hookom]

Try:
=IIf(Abs(Sum([Warranty Income]>=1)) = 0,0,Abs(Sum([Warranty
Income]))/Abs(Sum([Warranty Income]>=1)) )

--
Duane Hookom
Microsoft Access MVP

I'm trying to add the total income from the [Warranty Income] field, then to
divide that number by the number of instances that the field [Warranty
Income] had a value of greater than 1. ex. total income 1644 divided by 4
warranties sold to give a value of 411. However, if in any of the subgroups
that the report is running has no warranties sold, which would mean no
[Warranty Income] with a value greater than 1, the return I get is #Num!.
The expression I'm using is:
=Abs(Sum([Warranty Income]))/Abs(Sum([Warranty Income]>=1))

I can't figure if you are getting divide by zero issues or nulls. If it is
divide by 0 then use
=IIf( {some expression}=0,0, {another expression}/{some expression})
If it is a problem with Nulls, use Nz({another expression},{substitute
expression})

Duane Hookom
Microsoft Access MVP

That doesn't address my question. My problem is that if there are no records
to count in the fields in question I get a #Num! showing up on my report.
I'd rather have that return a "0" instead.

:

You might want to check the answer to the question posted immediately prior
to yours. If you don't find the answer, post back.

--
Duane Hookom
Microsoft Access MVP


:

Here is the expression I'm using. If there are no returns in the field
[Warranty Income] then I'm getting the #Num! error. If there are no
warranties in the range I'd like to have "0" returned instead of #Num!.
=Abs(Sum([Warranty Income])/Sum([Warranty Income]>=1))

 

[Reese]

Wow, that's complicated, but it worked like a charm. Thank you!

Try:
=IIf(Abs(Sum([Warranty Income]>=1)) = 0,0,Abs(Sum([Warranty
Income]))/Abs(Sum([Warranty Income]>=1)) )

--
Duane Hookom
Microsoft Access MVP

I'm trying to add the total income from the [Warranty Income] field, then to
divide that number by the number of instances that the field [Warranty
Income] had a value of greater than 1. ex. total income 1644 divided by 4
warranties sold to give a value of 411. However, if in any of the subgroups
that the report is running has no warranties sold, which would mean no
[Warranty Income] with a value greater than 1, the return I get is #Num!.
The expression I'm using is:
=Abs(Sum([Warranty Income]))/Abs(Sum([Warranty Income]>=1))

I can't figure if you are getting divide by zero issues or nulls. If it is
divide by 0 then use
=IIf( {some expression}=0,0, {another expression}/{some expression})
If it is a problem with Nulls, use Nz({another expression},{substitute
expression})

Duane Hookom
Microsoft Access MVP


:

That doesn't address my question. My problem is that if there are no records
to count in the fields in question I get a #Num! showing up on my report.
I'd rather have that return a "0" instead.

:

You might want to check the answer to the question posted immediately prior
to yours. If you don't find the answer, post back.

--
Duane Hookom
Microsoft Access MVP


:

Here is the expression I'm using. If there are no returns in the field
[Warranty Income] then I'm getting the #Num! error. If there are no
warranties in the range I'd like to have "0" returned instead of #Num!.
=Abs(Sum([Warranty Income])/Sum([Warranty Income]>=1))