#NUM! #ERROR!

[sara]

Help! I am not vey experienced, and am the only IT person
in my company, so I need help!

I have a report that shows Sales for This Year, Last Year,
the Difference (TY-LY) and the % Diff (DIFF/LY).

I have lots of .00 for TY and LY, and I'm getting #NUM! on
the report and #ERROR! in the Query.
And I have lots of .00 for LY and $$ for TY.

In the detail, I get blank. I am using
=IIf(IsError([TOTDIFF]/[TOTLY])," ",[TOTDIFF]/[TOTLY])

In the Summary, I get #NUM! (report). I calculate the
Total Difference, for example in a report field and name
the report field TOTDIFF. The for the % I am saying
TOTDIFF/TOTLY (also named).

I have the field formatted #.0%;(#.0%). This works in
many other reports.

I have searched all over, and can't find an answer to
help. Thanks.
Sara

 

[Ken Snell]

See Microsoft Knowledge Base Article - 209132
ACC2000: Troubleshooting Tips for Error Values
http://support.microsoft.com/default.aspx?scid=kb;en-us;209132

After looking through that, post back with additional questions that you
might have.

 

[Guest]

Thanks, That is one of the searches I performed. It says
that #ERROR means Access can't evaluate an expression -
though I don't really know what that means, and since it's
a lot of zeroes, it's ok in the Query, as long as it's
right on the report.

For #NUM, it says that the value in the field is too large
(either positively or negatively) to be stored in the
field, based on the field's DataType or FieldSize property
setting. Well, I don't think it is. It should be 100% in
some cases (sold $50 this year and 0 last year) and should
be blank in other cases 0/0. But I get blank in all
detail lines, with and without numbers, and #NUM in most
summary lines, though on some fields I get #Div/0.

Fields are formatted in the Query as #.0;(#.0).
I have another report that is much larger and it runs all
these just fine. In fact, I just copied that report,
changed the input query and some headings to run this one
that's not working. That's partly why I'm so baffled and
frustrated.
Thanks

-----Original Message-----
See Microsoft Knowledge Base Article - 209132
ACC2000: Troubleshooting Tips for Error Values
http://support.microsoft.com/default.aspx?scid=kb;en- us;209132

After looking through that, post back with additional questions that you
might have.
--

Ken Snell
<MS ACCESS MVP>

Help! I am not vey experienced, and am the only IT person
in my company, so I need help!

I have a report that shows Sales for This Year, Last Year,
the Difference (TY-LY) and the % Diff (DIFF/LY).

I have lots of .00 for TY and LY, and I'm getting #NUM! on
the report and #ERROR! in the Query.
And I have lots of .00 for LY and $$ for TY.

In the detail, I get blank. I am using
=IIf(IsError([TOTDIFF]/[TOTLY])," ",[TOTDIFF]/[TOTLY])

In the Summary, I get #NUM! (report). I calculate the
Total Difference, for example in a report field and name
the report field TOTDIFF. The for the % I am saying
TOTDIFF/TOTLY (also named).

I have the field formatted #.0%;(#.0%). This works in
many other reports.

I have searched all over, and can't find an answer to
help. Thanks.
Sara

.

 

[Ken Snell]

OK - the following is provided without knowing much about your table, query,
and report structures.

#ERROR likely means that you're using incorrect references in your
expression. In a report, you should use the field names, not control names,
in the expressions. Thus, if the difference is the difference between two
fields: TY and LY, then
=[TY]-[LY]
would be the control source of the control that is to display this
difference.

The percent difference control's control source must reuse the actual fields
in its expression:
=([TY]-[LY])/[LY]

Of course, to trap for possibility of LY being zero and to display a zero if
it is:
=IIf([LY]=0,0,([TY]-[LY])/[LY])

and so on for expressions.

It also could mean that you're trying to do arithmetic expressions when
mixing text and numeric data. To answer this, you need to look at the
formats of the fields in your tables.

#NUM! likely means that you are trying to mix numeric and text values and
put them into a numeric-formatted control.

How you format a query's fields' values has no effect on the actual format
of the data -- it just changes how the data are displayed.

Hoping that this helps a bit.
--

Ken Snell
<MS ACCESS MVP>

Thanks, That is one of the searches I performed. It says
that #ERROR means Access can't evaluate an expression -
though I don't really know what that means, and since it's
a lot of zeroes, it's ok in the Query, as long as it's
right on the report.

For #NUM, it says that the value in the field is too large
(either positively or negatively) to be stored in the
field, based on the field's DataType or FieldSize property
setting. Well, I don't think it is. It should be 100% in
some cases (sold $50 this year and 0 last year) and should
be blank in other cases 0/0. But I get blank in all
detail lines, with and without numbers, and #NUM in most
summary lines, though on some fields I get #Div/0.

Fields are formatted in the Query as #.0;(#.0).
I have another report that is much larger and it runs all
these just fine. In fact, I just copied that report,
changed the input query and some headings to run this one
that's not working. That's partly why I'm so baffled and
frustrated.
Thanks

-----Original Message-----
See Microsoft Knowledge Base Article - 209132
ACC2000: Troubleshooting Tips for Error Values
http://support.microsoft.com/default.aspx?scid=kb;en- us;209132

After looking through that, post back with additional questions that you
might have.
--

Ken Snell
<MS ACCESS MVP>

Help! I am not vey experienced, and am the only IT person
in my company, so I need help!

I have a report that shows Sales for This Year, Last Year,
the Difference (TY-LY) and the % Diff (DIFF/LY).

I have lots of .00 for TY and LY, and I'm getting #NUM! on
the report and #ERROR! in the Query.
And I have lots of .00 for LY and $$ for TY.

In the detail, I get blank. I am using
=IIf(IsError([TOTDIFF]/[TOTLY])," ",[TOTDIFF]/[TOTLY])

In the Summary, I get #NUM! (report). I calculate the
Total Difference, for example in a report field and name
the report field TOTDIFF. The for the % I am saying
TOTDIFF/TOTLY (also named).

I have the field formatted #.0%;(#.0%). This works in
many other reports.

I have searched all over, and can't find an answer to
help. Thanks.
Sara

.