Here are some of the causes of #Error on a report.
1. Control Name
Make sure the name of the control is not the same as the name of a field.
Access gets confused if the control name matches a field name, but it is
bound to something else.
2. Calculation
There could be an error in the calculation itself that causes the error. For
example, dividing by zero, an incomplete argument for a function such as
DLookup() when the value is Null, or an attempt to use a function that does
not accept Nulls, such as CDbl().
3. Inappropriate Format
If you format a control for a Number or Date, and the value it contains does
not match the format, you may see #Error.
4. Referring to non-existent controls
If the report returns no records at all, there are no controls for the
detail section, and so attempting to refer to them in any way (such as for
another calculation) generates an error. You can use IIf() and test the
HasData property of the report to avoid this condition.
5. Cascading Errors
Once Access is unable to calculate an expression, it gives up and shows
#Error for the remaining calculated controls. This does not mean that all of
them have an error--but one of them does. The simplest way to track them
down might be to make a copy of the report and begin eliminating the
calculated controls until you find the one that triggers the error.
HTH.
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users -
http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Andrew Chanter" <he****@radsolutions.com.au> wrote in message
news:05**************@news-server.bigpond.net.au...
I have an application that produces examination scores. Candidates have a
choice of sitting 1 of 2 subjects or both. I have produced a query that
gives all the results for candidates that sat subject A. This contains
some fairly complex calculations. My problem is that I need to produce a
report that shows the scores for all candidates regardless of whether they
sat the exam for subject A. This should be fairly easy to produce: simply
create a query that left joins the full candidate list to the query that
shows the results for those candidates that sat subject A. This should
therefore have nulls in all columns where a candidate did not attempt the
subject.
For the most part this works fine, but for some bizarre reason some
columns show #Error rather than the null result expected. These errors
are causing major headaches. Does anyone know what causes this and what I
can do to get around it?