Division By Zero Error

  • Thread starter Thread starter Aaron
  • Start date Start date
A

Aaron

Hi - I'm getting this error message when trying to run a report. I'm running
the report against a query. This is the following logic that seems to be
causing the error because if I remove it from the report filter, the report
runs. The report is not correct because it is supposed to only include data
that uses the criteria from the report filter.

Report Filter: NRF > 60

Query
NRF: IIf([REFIX_FREQ]>[RF],[REFIX_FREQ],[RF])
RF: Round(([AO CMR ARMS]!MTG_FLT_NXT_RST-[AO CMR ARMS]!asofDate)/30.41667)

REFIX_FREQ value ranges from 1 to 12

Any insights would be appreciated. Thank you!
 
From your expressions, I don't see what would cause a divide by zero. IMO,
don't use the name of a calculated column inside another column in the same
query. You are doing this with the RF column.

Also, IIf() returns a variant data type, so it is often adviseable to wrap
it in Val( IIf() ).
 
Hi Duane,

Thank you for the response. How do I get around not using a calculated
column inside another column in the same query? Are you suggesting to put it
in another query? Are there problems with keeping it in the same query?

Is it possible that a data format for a column or columns could cause this
type of error? Is there an easy way to check the format and value of the
data (i.e. sometimes when the field is blank it really isn't blank but
contains a hidden value)?

Thank you,
Aaron
Duane Hookom said:
From your expressions, I don't see what would cause a divide by zero. IMO,
don't use the name of a calculated column inside another column in the same
query. You are doing this with the RF column.

Also, IIf() returns a variant data type, so it is often adviseable to wrap
it in Val( IIf() ).

--
Duane Hookom
Microsoft Access MVP


Aaron said:
Hi - I'm getting this error message when trying to run a report. I'm running
the report against a query. This is the following logic that seems to be
causing the error because if I remove it from the report filter, the report
runs. The report is not correct because it is supposed to only include data
that uses the criteria from the report filter.

Report Filter: NRF > 60

Query
NRF: IIf([REFIX_FREQ]>[RF],[REFIX_FREQ],[RF])
RF: Round(([AO CMR ARMS]!MTG_FLT_NXT_RST-[AO CMR ARMS]!asofDate)/30.41667)

REFIX_FREQ value ranges from 1 to 12

Any insights would be appreciated. Thank you!
Click to expand...
Click to expand...
 
Hi Kevin,

Thank you for the response. Do you think that a data format could cause
this issue? I asked the same question to Duane in a earlier response.

Here are the other IIf statements that I use in the report:
I use these statements to show 0 if there is no data for this report.
=IIf([CurrFace] Is Null Or [CurrFace]=0,0,Count([CurrFace]))
=IIf([CurrFace] Is Null Or [CurrFace]=0,0,Sum([CurrFace]))

I use these IIf statements to place zeroes instead of showing the #Error
value when you try to calculate fields that have no data.
=IIf([WAC_5YR] Is Null Or [WAC_5YR]=0,0,Sum([WAC_5YR])/Sum([CurrFace]))
=IIf([WARM_5YR] Is Null Or [WARM_5YR]=0,0,Sum([WARM_5YR])/Sum([CurrFace]))

Is there another way to accomplish the same task?

Thank you very much for you help!
Aaron

kc-mass said:
I would look at the calculations on the report, not the query.

Reagrds

Kevin

Aaron said:
Hi - I'm getting this error message when trying to run a report. I'm
running
the report against a query. This is the following logic that seems to be
causing the error because if I remove it from the report filter, the
report
runs. The report is not correct because it is supposed to only include
data
that uses the criteria from the report filter.

Report Filter: NRF > 60

Query
NRF: IIf([REFIX_FREQ]>[RF],[REFIX_FREQ],[RF])
RF: Round(([AO CMR ARMS]!MTG_FLT_NXT_RST-[AO CMR ARMS]!asofDate)/30.41667)

REFIX_FREQ value ranges from 1 to 12

Any insights would be appreciated. Thank you!
Click to expand...
Click to expand...
 
You are checking the Numerator for 0 rather than the denominator.
Try replace:
=IIf([WAC_5YR] Is Null Or [WAC_5YR]=0,0,Sum([WAC_5YR])/Sum([CurrFace]))
with:
=IIf(Sum([CurrFace])=0,0,Sum([WAC_5YR])/Sum([CurrFace]))

--
Duane Hookom
Microsoft Access MVP


Aaron said:
Hi Kevin,

Thank you for the response. Do you think that a data format could cause
this issue? I asked the same question to Duane in a earlier response.

Here are the other IIf statements that I use in the report:
I use these statements to show 0 if there is no data for this report.
=IIf([CurrFace] Is Null Or [CurrFace]=0,0,Count([CurrFace]))
=IIf([CurrFace] Is Null Or [CurrFace]=0,0,Sum([CurrFace]))

I use these IIf statements to place zeroes instead of showing the #Error
value when you try to calculate fields that have no data.
=IIf([WAC_5YR] Is Null Or [WAC_5YR]=0,0,Sum([WAC_5YR])/Sum([CurrFace]))
=IIf([WARM_5YR] Is Null Or [WARM_5YR]=0,0,Sum([WARM_5YR])/Sum([CurrFace]))

Is there another way to accomplish the same task?

Thank you very much for you help!
Aaron

kc-mass said:
I would look at the calculations on the report, not the query.

Reagrds

Kevin

Aaron said:
Hi - I'm getting this error message when trying to run a report. I'm
running
the report against a query. This is the following logic that seems to be
causing the error because if I remove it from the report filter, the
report
runs. The report is not correct because it is supposed to only include
data
that uses the criteria from the report filter.

Report Filter: NRF > 60

Query
NRF: IIf([REFIX_FREQ]>[RF],[REFIX_FREQ],[RF])
RF: Round(([AO CMR ARMS]!MTG_FLT_NXT_RST-[AO CMR ARMS]!asofDate)/30.41667)

REFIX_FREQ value ranges from 1 to 12

Any insights would be appreciated. Thank you!
Click to expand...
Click to expand...
Click to expand...
 
Hi Duane,

Thank you for the info. I made the change to the report and still encounter
the problem. I know that you mentioned in another chain of messages that
there could a problem with too many calculated fields in the query. Could
this be the cause of the report not working properly? Do I need to limit the
calculated fields?

Thank you,
Aaron


Duane Hookom said:
You are checking the Numerator for 0 rather than the denominator.
Try replace:
=IIf([WAC_5YR] Is Null Or [WAC_5YR]=0,0,Sum([WAC_5YR])/Sum([CurrFace]))
with:
=IIf(Sum([CurrFace])=0,0,Sum([WAC_5YR])/Sum([CurrFace]))

--
Duane Hookom
Microsoft Access MVP


Aaron said:
Hi Kevin,

Thank you for the response. Do you think that a data format could cause
this issue? I asked the same question to Duane in a earlier response.

Here are the other IIf statements that I use in the report:
I use these statements to show 0 if there is no data for this report.
=IIf([CurrFace] Is Null Or [CurrFace]=0,0,Count([CurrFace]))
=IIf([CurrFace] Is Null Or [CurrFace]=0,0,Sum([CurrFace]))

I use these IIf statements to place zeroes instead of showing the #Error
value when you try to calculate fields that have no data.
=IIf([WAC_5YR] Is Null Or [WAC_5YR]=0,0,Sum([WAC_5YR])/Sum([CurrFace]))
=IIf([WARM_5YR] Is Null Or [WARM_5YR]=0,0,Sum([WARM_5YR])/Sum([CurrFace]))

Is there another way to accomplish the same task?

Thank you very much for you help!
Aaron

kc-mass said:
I would look at the calculations on the report, not the query.

Reagrds

Kevin

Hi - I'm getting this error message when trying to run a report. I'm
running
the report against a query. This is the following logic that seems to be
causing the error because if I remove it from the report filter, the
report
runs. The report is not correct because it is supposed to only include
data
that uses the criteria from the report filter.

Report Filter: NRF > 60

Query
NRF: IIf([REFIX_FREQ]>[RF],[REFIX_FREQ],[RF])
RF: Round(([AO CMR ARMS]!MTG_FLT_NXT_RST-[AO CMR ARMS]!asofDate)/30.41667)

REFIX_FREQ value ranges from 1 to 12

Any insights would be appreciated. Thank you!
Click to expand...
Click to expand...
Click to expand...
 
Hi Aaron

I would test the value of Sum([CurrFace]), it is almost certainly your
problem.

For debugging try IIF(Sum([CurrFace]) =0,1,Sum([CurrFace])) in your
formular.

I bet the problem goes away.

Regards

Kevin


Aaron said:
Hi Duane,

Thank you for the info. I made the change to the report and still
encounter
the problem. I know that you mentioned in another chain of messages that
there could a problem with too many calculated fields in the query. Could
this be the cause of the report not working properly? Do I need to limit
the
calculated fields?

Thank you,
Aaron


Duane Hookom said:
You are checking the Numerator for 0 rather than the denominator.
Try replace:
=IIf([WAC_5YR] Is Null Or [WAC_5YR]=0,0,Sum([WAC_5YR])/Sum([CurrFace]))
with:
=IIf(Sum([CurrFace])=0,0,Sum([WAC_5YR])/Sum([CurrFace]))

--
Duane Hookom
Microsoft Access MVP


Aaron said:
Hi Kevin,

Thank you for the response. Do you think that a data format could
cause
this issue? I asked the same question to Duane in a earlier response.

Here are the other IIf statements that I use in the report:
I use these statements to show 0 if there is no data for this report.
=IIf([CurrFace] Is Null Or [CurrFace]=0,0,Count([CurrFace]))
=IIf([CurrFace] Is Null Or [CurrFace]=0,0,Sum([CurrFace]))

I use these IIf statements to place zeroes instead of showing the
#Error
value when you try to calculate fields that have no data.
=IIf([WAC_5YR] Is Null Or [WAC_5YR]=0,0,Sum([WAC_5YR])/Sum([CurrFace]))
=IIf([WARM_5YR] Is Null Or
[WARM_5YR]=0,0,Sum([WARM_5YR])/Sum([CurrFace]))

Is there another way to accomplish the same task?

Thank you very much for you help!
Aaron

:

I would look at the calculations on the report, not the query.

Reagrds

Kevin

Hi - I'm getting this error message when trying to run a report.
I'm
running
the report against a query. This is the following logic that seems
to be
causing the error because if I remove it from the report filter,
the
report
runs. The report is not correct because it is supposed to only
include
data
that uses the criteria from the report filter.

Report Filter: NRF > 60

Query
NRF: IIf([REFIX_FREQ]>[RF],[REFIX_FREQ],[RF])
RF: Round(([AO CMR ARMS]!MTG_FLT_NXT_RST-[AO CMR
ARMS]!asofDate)/30.41667)

REFIX_FREQ value ranges from 1 to 12

Any insights would be appreciated. Thank you!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top