#Error on when ther is no data for Calculation

  • Thread starter Thread starter robboll
  • Start date Start date
R

robboll

I have an Access 2003 form where the on some records the calculation
works fine when all elements of the calculation have values. When
there are no values in one or more of the variables it results in a
#Error.

Is there a way to prevent that at the Form level?

Any help with this appreciated!

Rbollinger
 
No. You cannot prevent this at the form level. It is a matter of
understanding why the error occurs, and dealing with it on a case-by-case
basis.

On cause is mal-formed arguments. If you have:
=DLookup("MyField", "MyTable", "ID = " & [ID])
and you are at a new record where ID has not been assigned, the 3rd argument
resolves to just:
ID =
Clearly that is going to produce an error. To avoid that, use Nz() to supply
some value, e.g.:
=DLookup("MyField", "MyTable", "ID = " & Nz([ID],0))

If there are no records in the form, and no new records can be added, the
detail section of the form goes completely blank. The form header and footer
still show, and if you have a calculation that refers to the non-existent
controls in the detail section, they will error. There are convoluted ways
of trying to get around that; for example, you could try referring to
MyControl like this:
=IIf([Form].[RecordsetClone].[RecordCount]=0, Null, [MyControl])
However, this scenario is likely to thoroughly confuse Access anyway, so you
are still likely to run into problems. Details in:
Incorrect display of data
at:
http://allenbrowne.com/bug-06.html
So the simpler workaround might be to set the form's AllowAdditions property
back to Yes (so the detail section does not go blank), and cancel the
BeforeInsert event to prevent new records.

There are several other causes of #Error, such as division by zero, circular
dependencies, and so on.
 
Divide by Zero is probably one of the biggest offenders in this kind of
situation. The easy fix for that is to test for zero before doing the divide:

Me.txtBoxOne = Me.txtBoxTwo / IIf(IsNull(Me.txtBoxThree),1,Me.txtBoxThree)

Allen Browne said:
No. You cannot prevent this at the form level. It is a matter of
understanding why the error occurs, and dealing with it on a case-by-case
basis.

On cause is mal-formed arguments. If you have:
=DLookup("MyField", "MyTable", "ID = " & [ID])
and you are at a new record where ID has not been assigned, the 3rd argument
resolves to just:
ID =
Clearly that is going to produce an error. To avoid that, use Nz() to supply
some value, e.g.:
=DLookup("MyField", "MyTable", "ID = " & Nz([ID],0))

If there are no records in the form, and no new records can be added, the
detail section of the form goes completely blank. The form header and footer
still show, and if you have a calculation that refers to the non-existent
controls in the detail section, they will error. There are convoluted ways
of trying to get around that; for example, you could try referring to
MyControl like this:
=IIf([Form].[RecordsetClone].[RecordCount]=0, Null, [MyControl])
However, this scenario is likely to thoroughly confuse Access anyway, so you
are still likely to run into problems. Details in:
Incorrect display of data
at:
http://allenbrowne.com/bug-06.html
So the simpler workaround might be to set the form's AllowAdditions property
back to Yes (so the detail section does not go blank), and cancel the
BeforeInsert event to prevent new records.

There are several other causes of #Error, such as division by zero, circular
dependencies, and so on.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

robboll said:
I have an Access 2003 form where the on some records the calculation
works fine when all elements of the calculation have values. When
there are no values in one or more of the variables it results in a
#Error.

Is there a way to prevent that at the Form level?

Any help with this appreciated!

Rbollinger
Click to expand...
Click to expand...
 
Back
Top