Left Join Returning #Error

[allin1joe]

I have a query that I'm trying to return all Projects and the budget for any of those projects that exist. If a budget doesn't exist, I want to show 0s. For some reason, I can't get rid of #Error for these fields when the left join doesn't return something. Below is my query with all of the different attempts of handling this issue:

SELECT Project.UCMG_CD, 
    Project.SPRF, 
    Project.PROJECT_NAME, 
    iif(isnull(BudgetImport_IDSplit.Current_Prompt_Estimate),0,BudgetImport_IDSplit.Current_Prompt_Estimate) AS Current_Prompt_Estimate, 
    nz(BudgetImport_IDSplit.Hist_Spend,0) AS Hist_Spend,
 BudgetImport_IDSplit.Current_Prompt_Estimate-BudgetImport_IDSplit.Hist_Spend AS CY_Approve_UHGIT_Budget, 
    iif(iserror(BudgetImport_IDSplit.CY_UHGIT_Spend),0,BudgetImport_IDSplit.CY_UHGIT_Spend) AS CY_UHGIT_Spend,
    BudgetImport_IDSplit.CY_Remain_Spend AS CY_Remain_Spend,
   BudgetImport_IDSplit.CY_UHGIT_Spend+BudgetImport_IDSplit.CY_Remain_Spend AS CY_Total_Proj_Spend
FROM Project 
LEFT JOIN BudgetImport_IDSplit 
  ON Project.SPRF = BudgetImport_IDSplit.SPRF;

I've attached a screen shot of the data the query is returning. Any ideas?
[imgnothumb]http://bytes.com/attachments/attachment/6655d1350132937/left_join_error s.jpg[/imgnothumb]
Thanks

Joe

[allin1joe]

Sorry for the double post, but I just found some interesting information. Sounds like this is caused because I'm left joining with a query. With this being the case, here's the SQL behind the query:

SELECT IIf(Len(BudgetImport.F1)>9,
BudgetImport.F1,Null) AS UCMG_ID, 
  IIf(Len(BudgetImport.F1)=9,
BudgetImport.F1,Null) AS SPRF, 
  ccur(nz(BudgetImport.F4,0)) AS CY_UHG_IT_Alloc, 
  ccur(BudgetImport.F7) AS Current_Prompt_Estimate, 
  ccur(iif(BudgetImport.F9='$-',0,BudgetImport.F9)) AS Hist_Spend, 
  ccur(BudgetImport.F10) AS CY_UHGIT_Spend, 
  ccur(iif(BudgetImport.F12='$-',0,BudgetImport.F12)) AS CY_Remain_Spend
FROM BudgetImport;

The reason for the IIF translating the $- value is that this table is populated with an import from Excel. For this one particular column, 0s were shown as a $- so the CCUR translation was erroring out.

For now, I'm going to try to re-write the query to include this basic information from Project to see if it works better. If anyone has any better ideas, I'm all ears :).

Thanks

Joe

[allin1joe]

Well, I solved my own issue, but it's not the solution I wanted. I created a query with the following SQL:

SELECT Project.UCMG_CD, Project.SPRF, Project.PROJECT_NAME, IIF(ISNULL(BudgetImport.F7),0,CCur(BudgetImport.F7)) AS Current_Prompt_Estimate, IIF(ISNULL(BudgetImport.F9),0,CCur(IIf(BudgetImport.F9='$-',0,BudgetImport.F9))) AS Hist_Spend, IIF(ISNULL(BudgetImport.F10),0,CCur(BudgetImport.F10)) AS CY_UHGIT_Spend, IIF(ISNULL(BudgetImport.F12),0,CCur(IIf(BudgetImport.F12='$-',0,BudgetImport.F12))) AS CY_Remain_Spend
FROM Project LEFT JOIN BudgetImport ON Project.SPRF = BudgetImport.F1
WHERE Project.SPRF NOT IN ('TBD','N/A');

I then changed my report query to this:

SELECT Project.UCMG_CD, Project.SPRF, Project.PROJECT_NAME, Budget_IT_ByProject.Current_Prompt_Estimate, Budget_IT_ByProject.Hist_Spend, Budget_IT_ByProject.Current_Prompt_Estimate-Budget_IT_ByProject.Hist_Spend AS CY_Approve_UHGIT_Budget, Budget_IT_ByProject.CY_UHGIT_Spend, Budget_IT_ByProject.CY_UHGIT_Spend+Budget_IT_ByProject.CY_Remain_Spend AS CY_Total_Proj_Spend
FROM Project LEFT JOIN Budget_IT_ByProject ON Project.SPRF = Budget_IT_ByProject.SPRF;

The lesson learned here is that when you left join to a query, Access might not like it and throw an error on any column in the query that isn't returned by the join.

Thanks

Joe

[zmbd]

The reason for the IIF translating the $- value is that this table is populated with an import from Excel. For this one particular column, 0s were shown as a $- so the CCUR translation was erroring out.

You answered your question.
It is not the left joins that were the issue; instead, it was the incomming data. Anytime you get a "#ERROR" you need to go back to the underlying data and verify that it is correct for the function(s) being used or that there isn't some division by zero that crept in.