Zero (0) values not showing in query result

Joined
Oct 9, 2018
Messages
44
I have made a query based on comoboxes and checkboxes. One of the checkboxes is chkExternal. When it's checked I want all the scores from all employees to appear: Internal + External Employees, or 0 en -1 values. When it's not checked I want only 0 values to appear: only Internal.

This is the full SQL:

SELECT dbo_tblScore.Competence_Knowledge, dbo_tblEmployee.Name, dbo_tblScore.Score, dbo_tblEmployee.External


FROM (((dbo_tblScore INNER JOIN dbo_tblCompetence_Profile ON dbo_tblScore.Competence_Profile_ID = dbo_tblCompetence_Profile.Competence_Profile_ID) INNER JOIN dbo_tblLink_Employee_Function ON dbo_tblCompetence_Profile.Link_Employee_Function_ID = dbo_tblLink_Employee_Function.Link_Employee_Function_ID) INNER JOIN dbo_tblEmployee ON dbo_tblLink_Employee_Function.Employee_ID = dbo_tblEmployee.Employee_ID) INNER JOIN dbo_tblCategory ON dbo_tblScore.Category_ID = dbo_tblCategory.Category_ID


WHERE (((IIf(IsNull([Forms]![frmExportReport]![cmbFilter_Team]),True,[Forms]![frmExportReport]![cmbFilter_Team]=[dbo_tblCompetence_Profile].[Team_ID]))<>False) AND ((IIf(IsNull([Forms]![frmExportReport]![cmbFilter_Functie]),True,[Forms]![frmExportReport]![cmbFilter_Functie]=[dbo_tblScore].[Function_ID]))<>False) AND ((IIf(IsNull([Forms]![frmExportReport]![cmbFilter_CategoryType]),True,[Forms]![frmExportReport]![cmbFilter_CategoryType]=[dbo_tblCategory].[CategoryType_ID]))<>False) AND ((IIf(IsNull([Forms]![frmExportReport]![cmbFilter_Category].[Value]),True,[Forms]![frmExportReport]![cmbFilter_Category]=[dbo_tblCategory].[Category_ID]))<>False))
GROUP BY dbo_tblScore.Competence_Knowledge, dbo_tblEmployee.Name, dbo_tblScore.Score, dbo_tblEmployee.OutOfService, dbo_tblEmployee.Internal
HAVING (((dbo_tblEmployee.OutOfService)=IIf([Forms]![frmExportReport]![chkOutOfService]=True,-1,0)) AND ((dbo_tblEmployee.External)=IIf([Forms]![frmExportReport]![chkExternal]=True,([dbo_tblEmployee].[External]) Is Not Null,0)))
ORDER BY dbo_tblEmployee.Name;

The part where I'm talking about:
((dbo_tblEmployee.External)=IIf([Forms]![frmExportReport]![chkExternal]=True,([dbo_tblEmployee].[External]) Is Not Null,0))

I have tried to replace 'Is Not Null' by 'Like ("*")', Not is null, '<1', '>-2',... I ran out of options... Any more ideas?

Thanks guys!
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

Watch MrExcel Video

Forum statistics

Threads
1,090,386
Messages
5,414,142
Members
403,515
Latest member
see_mido

This Week's Hot Topics

Top