How do I replace error messages in Access?

[Guest]

Can anyone tell me what I can do to replace the standard error messages that
occur in my reports? For example when deviding totals by zero the #Num!
error appears and I was hoping to include an IIf statement (or the likes of)
to replace the message with something more suitable.

 

[BruceM]

You may find the Nz function useful in the particular instance you describe.
Help has more information about this, but in general it may go something
like this:

=[FirstField]/Nz([SecondField],1)

If [SecondField] is 0, a 1 is used as the denominator in place of the 0.

That would be in an unbound text box. In a calculated query field it would
be:

NewField: [FirstField]/Nz([SecondField],1)

You could use a message box to advise users that they can't use a 0, but the
question becomes where to put the message. If it is in the After Update
event for txtSecondField (the text box bound to [SecondField]) it will run
only if users update the field. You could use the control's Exit event,
assuming that the users go to that control. You could use the AfterUpdate
event for txtFirstField to set the focus to txtSecondField. If you are
using a command button to perform the calculation, you could use its Click
event. And so forth. In any case, maybe something like:

If IsNull[SecondField] Then
msgbox "Can't be 0"
Cancel = True
Else
Me.FirstField/Me.SecondField
End If


"Charles at Cambridge Food"

 

[Guest]

Dear Bruce

Thanks for your reply. In my reports I often have to calculate the
difference between an individual department’s turnover from a month in a
preceding year to the same year of the following year. Calculating the
difference with simple subtraction is no problem because 0 – 0 = 0. The
problem is in calculating the % difference i.e. zero divided by zero when the
Microsoft message appears because zero cannot be divided by zero. What
statement / function can I insert to override the error message on the report
itself? I would like to say something like if error then return 0 or
something easier on the eye. I have tried several different functions and
IIf statements and I have not been able to get rid of the MS error message.

I’m hoping you will be able to help me with this.

Best regards

Charles

You may find the Nz function useful in the particular instance you describe.
Help has more information about this, but in general it may go something
like this:

=[FirstField]/Nz([SecondField],1)

If [SecondField] is 0, a 1 is used as the denominator in place of the 0.

That would be in an unbound text box. In a calculated query field it would
be:

NewField: [FirstField]/Nz([SecondField],1)

You could use a message box to advise users that they can't use a 0, but the
question becomes where to put the message. If it is in the After Update
event for txtSecondField (the text box bound to [SecondField]) it will run
only if users update the field. You could use the control's Exit event,
assuming that the users go to that control. You could use the AfterUpdate
event for txtFirstField to set the focus to txtSecondField. If you are
using a command button to perform the calculation, you could use its Click
event. And so forth. In any case, maybe something like:

If IsNull[SecondField] Then
msgbox "Can't be 0"
Cancel = True
Else
Me.FirstField/Me.SecondField
End If


"Charles at Cambridge Food"

Can anyone tell me what I can do to replace the standard error messages
that
occur in my reports? For example when deviding totals by zero the #Num!
error appears and I was hoping to include an IIf statement (or the likes
of)
to replace the message with something more suitable.

 

[John Spencer]

It would help if you posted the calculation you are using.

Generically, the statement would look something like the following in an
SQL statement.

IIF(SomeNumber =0,0,SomeOtherNumber/SomeNumber)

In VBA, you have to handle that a bit differently, as the above will always
generate an error when SomeNumber is zero.

IIF(SomeNumber=0,0,SomeOtherNumber/IIF(SomeNumber=0,1,SomeNumber))

Of course if you always wanted to return 1 when SomeNumber was zero, you
could use
SomeOtherNumber/ IIF(SomeNumber=0,SomeOtherNumber,SomeNumber)


"Charles at Cambridge Food"

Dear Bruce

Thanks for your reply. In my reports I often have to calculate the
difference between an individual department's turnover from a month in a
preceding year to the same year of the following year. Calculating the
difference with simple subtraction is no problem because 0 - 0 = 0. The
problem is in calculating the % difference i.e. zero divided by zero when
the
Microsoft message appears because zero cannot be divided by zero. What
statement / function can I insert to override the error message on the
report
itself? I would like to say something like if error then return 0 or
something easier on the eye. I have tried several different functions and
IIf statements and I have not been able to get rid of the MS error
message.

I'm hoping you will be able to help me with this.

Best regards

Charles

You may find the Nz function useful in the particular instance you
describe.
Help has more information about this, but in general it may go something
like this:

=[FirstField]/Nz([SecondField],1)

If [SecondField] is 0, a 1 is used as the denominator in place of the 0.

That would be in an unbound text box. In a calculated query field it
would
be:

NewField: [FirstField]/Nz([SecondField],1)

You could use a message box to advise users that they can't use a 0, but
the
question becomes where to put the message. If it is in the After Update
event for txtSecondField (the text box bound to [SecondField]) it will
run
only if users update the field. You could use the control's Exit event,
assuming that the users go to that control. You could use the
AfterUpdate
event for txtFirstField to set the focus to txtSecondField. If you are
using a command button to perform the calculation, you could use its
Click
event. And so forth. In any case, maybe something like:

If IsNull[SecondField] Then
msgbox "Can't be 0"
Cancel = True
Else
Me.FirstField/Me.SecondField
End If


"Charles at Cambridge Food"

Can anyone tell me what I can do to replace the standard error messages
that
occur in my reports? For example when deviding totals by zero the
#Num!
error appears and I was hoping to include an IIf statement (or the
likes
of)
to replace the message with something more suitable.