#Error message when there is no related data

[darall]

I have two tables; one that contains time spent on projects by resource and the second is a master list of resources.

I have a query that relates the two tables with the Resource ID where it includes all records from tblListResources and the related records from tblProjectHours:

Joe Project A 15
Joe Project B 15
Jane <No Time Tracked>
Frank Project B 25


When I pull the hours in to the query and run the report, any resource that did not complete their time displays a #Error message in the hours field. Instead I want to display a zero if they have not filled in hours.

I tried the nz function: Hours: nz([sngHours],0)

But still get the error.

 

[lespaul]

what's the SQL of the query?

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[darall]

Here is the query I am having problems with:

SELECT qryList_Resources_TS.strR_FullName AS Resource, qryHours_Available_SelectMonth.M01_Available, qryList_Resources_TS.strL1_Team, qryList_Resources_TS.strL2_Team, qryList_Resources_TS.strL3_Team
FROM qryList_Resources_TS LEFT JOIN qryHours_Available_SelectMonth ON qryList_Resources_TS.BMTName = qryHours_Available_SelectMonth.strResource;

Here is the code for the qryHours_Available_SelectMonth query that the above is based on:

 SELECT qryHours_Allocated_Unique.strResource, qryHours_Allocated_Unique.dtmCurrentMonth, (AvWorkDays([dtmCurrentMonth])*8)-nz([SumOfLastOfintHours_M01],0) AS M01_Available, (AvWorkDays(DateAdd("m",1,[dtmCurrentMonth]))*8)-[SumOfLastOfintHours_M02] AS M02_Available, (AvWorkDays(DateAdd("m",2,[dtmCurrentMonth]))*8)-[SumOfLastOfintHours_M03] AS M03_Available, (AvWorkDays(DateAdd("m",3,[dtmCurrentMonth]))*8)-[SumOfLastOfintHours_M04] AS M04_Available, (AvWorkDays(DateAdd("m",4,[dtmCurrentMonth]))*8)-[SumOfLastOfintHours_M05] AS M05_Available, (AvWorkDays(DateAdd("m",5,[dtmCurrentMonth]))*8)-[SumOfLastOfintHours_M06] AS M06_Available, (AvWorkDays(DateAdd("m",6,[dtmCurrentMonth]))*8)-[SumOfLastOfintHours_M07] AS M07_Available, (AvWorkDays(DateAdd("m",7,[dtmCurrentMonth]))*8)-[SumOfLastOfintHours_M08] AS M08_Available, (AvWorkDays(DateAdd("m",8,[dtmCurrentMonth]))*8)-[SumOfLastOfintHours_M09] AS M09_Available, (AvWorkDays(DateAdd("m",9,[dtmCurrentMonth]))*8)-[SumOfLastOfintHours_M10] AS M10_Available, (AvWorkDays(DateAdd("m",10,[dtmCurrentMonth]))*8)-[SumOfLastOfintHours_M11] AS M11_Available, (AvWorkDays(DateAdd("m",11,[dtmCurrentMonth]))*8)-[SumOfLastOfintHours_M12] AS M12_Available
FROM qryHours_Allocated_Unique
WHERE (((qryHours_Allocated_Unique.dtmCurrentMonth)=[Forms]![m_frmReport_AvailableHours]![lstMonth]));

Here is the code for the qryList_Resources_TS query that the above is based on:

SELECT z_tblList_Resources.intR_ID, z_tblList_Resources.strR_UserName, z_tblList_Resources.strR_FN, z_tblList_Resources.strR_LN, z_tblList_Resources.strR_LN & ", " & z_tblList_Resources.strR_FN AS strR_FullName, IIf(z_tblList_Resources.strR_BMTName Is Null,z_tblList_Resources.strR_LN & ", " & z_tblList_Resources.strR_FN,z_tblList_Resources.strR_BMTName) AS BMTName, L3_Manager.intR_ID AS intMGR_ID, L3_Manager.strR_UserName AS strMGR_UserName, L3_Manager.strR_FN AS strMGR_FN, L3_Manager.strR_LN AS strMGR_LN, L3_Manager.strR_LN & ", " & L3_Manager.strR_FN AS strMGR_FullName, L2_Director.intR_ID AS intDIR_ID, L2_Director.strR_UserName AS strDIR_UserName, L2_Director.strR_FN AS strDIR_FN, L2_Director.strR_LN AS strDIR_LN, L2_Director.strR_LN & ", " & L2_Director.strR_FN AS strDIR_FullName, L1_VP.intR_ID AS intVP_ID, L1_VP.strR_UserName AS strVP_UserName, L1_VP.strR_FN AS strVP_FN, L1_VP.strR_LN AS strVP_LN, L1_VP.strR_LN & ", " & L1_VP.strR_FN AS strVP_FullName, z_tbList_Resources_L1.strL1_Team, z_tbList_Resources_L2.strL2_Team, z_tbList_Resources_L3.strL3_Team, IIf([strL1_Team] Is Null,"",IIf([strL2_team] Is Null,[strL1_Team],[strL1_Team] & " - " & [strL2_team])) AS Workteam, z_tblList_Resources.ysnR_LeftCardinal
FROM z_tblList_Resources AS L1_VP RIGHT JOIN ((z_tbList_Resources_L1 RIGHT JOIN (z_tblList_Resources AS L2_Director RIGHT JOIN z_tbList_Resources_L2 ON L2_Director.intR_ID = z_tbList_Resources_L2.intL2_Name_ID) ON z_tbList_Resources_L1.intL1_ID = z_tbList_Resources_L2.intL1_L1_ID) RIGHT JOIN ((z_tblList_Resources AS L3_Manager RIGHT JOIN z_tbList_Resources_L3 ON L3_Manager.intR_ID = z_tbList_Resources_L3.intL3_Name_ID) RIGHT JOIN z_tblList_Resources ON z_tbList_Resources_L3.intL3_ID = z_tblList_Resources.intR_L3_ID) ON z_tbList_Resources_L2.intL2_ID = z_tbList_Resources_L3.intL3_L2_ID) ON L1_VP.intR_ID = z_tbList_Resources_L1.intL1_Name_ID
WHERE (((z_tbList_Resources_L1.strL1_Team) Like "TS - *") AND ((z_tblList_Resources.ysnR_LeftCardinal)=No))
ORDER BY z_tblList_Resources.intR_ID;