show zero value in query for all no data field in joined table

[Myint Aye]

Hi All :
Greeting from New Form member.
I would like to get suggestion or help in forming query.
I have made 2 tables employee data and leave record.I made query for all employees and their leave taken. In leave record table, some employee took leave and some din not take leave . Leave record shows only employees who took leave, I made outer join in employee register number joining type is 2 Include ALL records from 'employee data' and only those records from 'leave record' where the joined fields are equal.The query shows all employee register number but leave day shows empty for all employees .
I would like to show all employees who took leave days and zero who did not take leave .
Could you pls give example conditional expression for required field.



Thanks for all
Myint Aye

[June7]

Try Nz() function: Nz([fieldname],0)

Review http://allenbrowne.com/QueryPerfIssue.html#Nz

[Myint Aye]

Try Nz() function: Nz([fieldname],0)

Review http://allenbrowne.com/QueryPerfIssue.html#Nz

Hi:
I make the following.
SELECT EMPLOYEE.[EMPLOYEE NO], IIf([Query2].[SumOfPERIOD] Is Null,0,[Query2].[SumOfPERIOD]) AS Expr1
FROM EMPLOYEE LEFT JOIN Query2 ON EMPLOYEE.[EMPLOYEE NO] = Query2.[EMPLOYEE NO];
all record from Exper1 field from new query show zero value although there are somerecord in [Query2].[SomOfPERIOD] have numeric values.
could you pls correct it.
T&R
Myint Aye

[orange]

Here is a mockup using my tables.

SELECT tblGEmployee.EmpID
, Iif ( IsNull(Sum(EmpLeave.[LeaveTakenDays])),0,Sum(EmpLeave.[LeaveTakenDays]) ) as TotalTaken
FROM  tblGEmployee Left JOIN EmpLeave 
ON EmpLeave.EmpID = tblGEmployee.EmpID
GROUP BY tblGEmployee.EmpID;

Good luck with your project.

[Myint Aye]

Hi Orange.
Thank you for your reply . That is the point I need . I have assigned employee no as short text and When I make query that is OK .But when I join table with query to produce report ,It still show zero value . I suspect my query and when I change query as table , The employee no shows ID number (not employee no)and the system cannot get matched join field with employee.employee no then it show zero value . I have made new file in which employee no is number format. Then it works properly . I would like to say thanks again for your help.

[orange]

You are very welcome.
Glad you have a solution.