Expressions that Return "#Num!"

[Reese]

I have expressions in my report that return a value of "#Num!" There are no
sales recorded in the fields that they are derived from, but I'd like the
calculations in these fields to return a value of "$0.00" instead of "#Num!".
Can you help?

 

[Duane Hookom]

Are you getting this result when the report doesn't return any records?
What are the control sources of the text boxes that result in #Num?

 

[Reese]

Yes. The default value for the field is 0.00 if no sale of that product was
made to that customer. If no sales were made of that product to any
customers in that range then I get the #Num! return. If at least one
customer in the range bought the product, the calculation will work properly.
The control source for the field in questiuon is [Disability Income] which
comes from a query pulling from the Table.

 

[Duane Hookom]

Normally you don't see the #num error when control is bound to a field from
your report's record source. Where does [Disability Income] come from?

If you want to display a zero in a report header or footer where there are
no records returned, you must use the HasData property of the report like:
=IIf([HasData], [Some Expression],0)
--
Duane Hookom
Microsoft Access MVP

Yes. The default value for the field is 0.00 if no sale of that product was
made to that customer. If no sales were made of that product to any
customers in that range then I get the #Num! return. If at least one
customer in the range bought the product, the calculation will work properly.
The control source for the field in questiuon is [Disability Income] which
comes from a query pulling from the Table.

Are you getting this result when the report doesn't return any records?
What are the control sources of the text boxes that result in #Num?

 

[Reese]

Here is the formula I'm using.
=Abs(Sum([Disability Income])/[text201])
I'm trying to get it to figure out the average income from disability
insurance sales. Text 201 reference is a calculation on the report where the
formula is:
=Abs(Sum([Disability Income]>=1)) which counts how many disability contracts
we sold.

Normally you don't see the #num error when control is bound to a field from
your report's record source. Where does [Disability Income] come from?

If you want to display a zero in a report header or footer where there are
no records returned, you must use the HasData property of the report like:
=IIf([HasData], [Some Expression],0)
--
Duane Hookom
Microsoft Access MVP

Yes. The default value for the field is 0.00 if no sale of that product was
made to that customer. If no sales were made of that product to any
customers in that range then I get the #Num! return. If at least one
customer in the range bought the product, the calculation will work properly.
The control source for the field in questiuon is [Disability Income] which
comes from a query pulling from the Table.

Are you getting this result when the report doesn't return any records?
What are the control sources of the text boxes that result in #Num?

--
Duane Hookom
Microsoft Access MVP


:

I have expressions in my report that return a value of "#Num!" There are no
sales recorded in the fields that they are derived from, but I'd like the
calculations in these fields to return a value of "$0.00" instead of "#Num!".
Can you help?

 

[Duane Hookom]

Apparently the control source wasn't [Disability Income] as stated earlier.
Did you try my suggestion using the [HasData] expression?

--
Duane Hookom
Microsoft Access MVP

Here is the formula I'm using.
=Abs(Sum([Disability Income])/[text201])
I'm trying to get it to figure out the average income from disability
insurance sales. Text 201 reference is a calculation on the report where the
formula is:
=Abs(Sum([Disability Income]>=1)) which counts how many disability contracts
we sold.

Normally you don't see the #num error when control is bound to a field from
your report's record source. Where does [Disability Income] come from?

If you want to display a zero in a report header or footer where there are
no records returned, you must use the HasData property of the report like:
=IIf([HasData], [Some Expression],0)
--
Duane Hookom
Microsoft Access MVP

Yes. The default value for the field is 0.00 if no sale of that product was
made to that customer. If no sales were made of that product to any
customers in that range then I get the #Num! return. If at least one
customer in the range bought the product, the calculation will work properly.
The control source for the field in questiuon is [Disability Income] which
comes from a query pulling from the Table.

:

Are you getting this result when the report doesn't return any records?
What are the control sources of the text boxes that result in #Num?

--
Duane Hookom
Microsoft Access MVP


:

I have expressions in my report that return a value of "#Num!" There are no
sales recorded in the fields that they are derived from, but I'd like the
calculations in these fields to return a value of "$0.00" instead of "#Num!".
Can you help?

 

[Reese]

Yes, I tried you suggestion about the "Has Data" and it did not solve the
problem. In fact, the control source was [disability income]. The problem
was my reference to [text 201] which was an expression in another part of the
report. I wanted my expression to be divided by the result of [text 201].
So, I solved the problem by re-writing the expression contained in the field
[text 201], then including in in my new expression and it worked. Thank you
for your help.

Apparently the control source wasn't [Disability Income] as stated earlier.
Did you try my suggestion using the [HasData] expression?

--
Duane Hookom
Microsoft Access MVP

Here is the formula I'm using.
=Abs(Sum([Disability Income])/[text201])
I'm trying to get it to figure out the average income from disability
insurance sales. Text 201 reference is a calculation on the report where the
formula is:
=Abs(Sum([Disability Income]>=1)) which counts how many disability contracts
we sold.

Normally you don't see the #num error when control is bound to a field from
your report's record source. Where does [Disability Income] come from?

If you want to display a zero in a report header or footer where there are
no records returned, you must use the HasData property of the report like:
=IIf([HasData], [Some Expression],0)
--
Duane Hookom
Microsoft Access MVP


:

Yes. The default value for the field is 0.00 if no sale of that product was
made to that customer. If no sales were made of that product to any
customers in that range then I get the #Num! return. If at least one
customer in the range bought the product, the calculation will work properly.
The control source for the field in questiuon is [Disability Income] which
comes from a query pulling from the Table.

:

Are you getting this result when the report doesn't return any records?
What are the control sources of the text boxes that result in #Num?

--
Duane Hookom
Microsoft Access MVP


:

I have expressions in my report that return a value of "#Num!" There are no
sales recorded in the fields that they are derived from, but I'd like the
calculations in these fields to return a value of "$0.00" instead of "#Num!".
Can you help?