Zero (0) values not showing in query result

Roy_Excel_Island_Apps

Board Regular
Joined
Oct 9, 2018
Messages
52
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!
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

Roy_Excel_Island_Apps

Board Regular
Joined
Oct 9, 2018
Messages
52
FOUND THE SOLUTION!

WHERE (((IIf([Forms]![frmExportReport]![chkInternal]=True,True,[dbo_tblEmployee].[Internal]=True))<>False)
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,477
Messages
5,831,900
Members
430,090
Latest member
bjonesh2o

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top