#Error on when ther is no data for Calculation

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.

--
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.

"robboll" wrote in message
oups.com...
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

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" wrote:

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.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"robboll" wrote in message
oups.com...
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