Using the Expression Builder to deal with Errors

[jpress]

I want to be able to deal with a #Error in an IIf statement so that if there is a #Error I can show a different value and I can't seem to figure out how to match what to me looks like #Error. Is there an iserror function or something I can use?

Thanks.

 

[Dugantrain]

What you need to be doing is to build your Iif statement so that the Error never occurs in the first place. Division by 0 is a good example:

Iif([Your_Denominator]=0,0,[Your_Numerator]/[Your_Denominator])

The example above would return 0 if the denominator is 0 without actually performing any division. If the denominator is not 0, then it would divide normally. What exactly are you doing to get #Error?

 

[jpress]

The #Error seems to be coming up from a join....it happens in cases where there is nothing on the other side of the join. Usually these come up as Nulls but for some reason, in this case, it isn't. I'm still banging my head on this one if anyone has a suggestion.

 

[Dugantrain]

Can you post your SQL Statement?

 

[jpress]

Sorry this is so messy. I've bolded the field where the #Error is coming up. I really appreciate any advice you have.


SELECT [Customer Name Level Metrics with Bucket].[CP Risk Region], [Customer Name Level Metrics with Bucket].[Industry Group], [Customer Name Level Metrics with Bucket].UCN, PM_Researcher_Mapping.PARENT_UCN, IIf([Customer Name Level Metrics with Bucket]![CP Risk Region] Like "North America",[PM_Researcher_Mapping]![Portfolio Manager],"") AS [Portfolio Manager], IIf([Customer Name Level Metrics with Bucket]![CP Risk Region] Like "North America",[PM_Researcher_Mapping]![Research Analyst],"") AS [Research Analyst], [Customer Name Level Metrics with Bucket].Name, PM_Researcher_Mapping.PARENT_NAME, [Customer Name Level Metrics with Bucket].Industry AS [Industry Division], [Customer Name Level Metrics with Bucket].Obligor, [Customer Name Level Metrics with Bucket].MDY, [Customer Name Level Metrics with Bucket].[S&P], [Customer Name Level Metrics with Bucket].[Net CPM Exposure for Threshold], [Parent Name Level Metrics].[Net CPM Exposure for Threshold] AS [Parent Net CPM Exposure], IIf([Violator File]![Net PCE Violator] Like "Y","N ","") & IIf([Violator File]![Gross PCE Violator] Like "Y","G ","") & IIf([Violator File]![Net Capital Violator] Like "Y","C ","") AS [Violator Status], [EDF Query].EDF_TWO_MONTHS_AGO AS [EDF Two Months Ago], [EDF Query].EDF_LAST_MONTH AS [EDF Last Month], [EDF Query].CURRENT_EDF, ([EDF Query]![CURRENT_EDF]-[EDF Query]![EDF_LAST_MONTH])/[EDF Query]![EDF_LAST_MONTH] AS [1 MONTH %AGE CHANGE,

[Final Indices].[BUCKET MEAN EDF],

([EDF Query]![ln of current edf]-[Final Indices]![BUCKET MEAN OF LN EDF])/[Final Indices]![BUCKET ST DEV] AS [# of Deviations from Mean]
FROM ((([Customer Name Level Metrics with Bucket] LEFT JOIN ((PM_Researcher_Mapping LEFT JOIN [Parent Name Level Metrics] ON PM_Researcher_Mapping.PARENT_UCN = [Parent Name Level Metrics].UCN) LEFT JOIN [Violator File] ON PM_Researcher_Mapping.PARENT_UCN = [Violator File].[Parent UCN]) ON [Customer Name Level Metrics with Bucket].UCN = PM_Researcher_Mapping.UCN) LEFT JOIN [EDF Query] ON [Customer Name Level Metrics with Bucket].UCN = [EDF Query].UCN) LEFT JOIN [Final Indices] ON ([Customer Name Level Metrics with Bucket].Bucket = [Final Indices].Bucket) AND ([Customer Name Level Metrics with Bucket].[Industry Group] = [Final Indices].[FINAL INDUSTRY GROUP])) LEFT JOIN Rating_Indices ON [Customer Name Level Metrics with Bucket].Bucket = Rating_Indices.Bucket
WHERE ((([Customer Name Level Metrics with Bucket].Obligor) Not In ("8","9","10")) AND (([Parent Name Level Metrics].[Net CPM Exposure for Threshold])>10000) AND (([EDF Query].CURRENT_EDF) Is Not Null))
ORDER BY [Customer Name Level Metrics with Bucket].[CP Risk Region], [Customer Name Level Metrics with Bucket].[Industry Group], ([EDF Query]![ln of current edf]-[Final Indices]![BUCKET MEAN OF LN EDF])/[Final Indices]![BUCKET ST DEV] DESC;

 

[Dugantrain]

That's a pretty complex statement, but I do see that you are performing some division. Could it possibly be division by 0 that's giving you the error?