|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
June 20th, 2009, 12:38 AM
posted to microsoft.public.access.reports
|
|||
|
|||
Division By Zero Error
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! 
|
|
#2
June 20th, 2009, 09:43 PM
posted to microsoft.public.access.reports
|
|||
|
|||
|
Division By Zero Error
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" wrote: 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!
|
|
#3
June 21st, 2009, 06:03 PM
posted to microsoft.public.access.reports
|
|||
|
|||
|
Division By Zero Error
I would look at the calculations on the report, not the query.
Reagrds Kevin "Aaron" wrote in message ... 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!
|
|
#4
June 22nd, 2009, 08:54 PM
posted to microsoft.public.access.reports
|
|||
|
|||
|
Division By Zero Error
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" wrote: 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" wrote: 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!
|
|
#5
June 22nd, 2009, 09:06 PM
posted to microsoft.public.access.reports
|
|||
|
|||
|
Division By Zero Error
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" wrote: I would look at the calculations on the report, not the query. Reagrds Kevin "Aaron" wrote in message ... 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!
|
|
#6
June 22nd, 2009, 10:38 PM
posted to microsoft.public.access.reports
|
|||
|
|||
|
Division By Zero Error
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" wrote: 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" wrote: I would look at the calculations on the report, not the query. Reagrds Kevin "Aaron" wrote in message ... 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!
|
|
#7
June 23rd, 2009, 12:46 AM
posted to microsoft.public.access.reports
|
|||
|
|||
|
Division By Zero Error
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" wrote: 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" wrote: 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" wrote: I would look at the calculations on the report, not the query. Reagrds Kevin "Aaron" wrote in message ... 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!
|
|
#8
June 23rd, 2009, 07:07 PM
posted to microsoft.public.access.reports
|
|||
|
|||
|
Division By Zero Error
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" wrote in message news  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" wrote: 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" wrote: 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" wrote: I would look at the calculations on the report, not the query. Reagrds Kevin "Aaron" wrote in message ... 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!
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
