Complex Query using IF function in Google Sheets

Question

I'm trying to create a dynamic dashboard.

I've been able to create a query to filter for some of the information, but not all. What I'm trying to do is use the dashboard (the copy I shared is a slimmed-down version without charts) to select the collection period (this works), grade (this works) and IEP. I can get the IEP to query for accommodated or modified, but what I would like is to be able to select all IEPs that would combine the two (basically where that column is not null), and a query where is selected no IEPs (where the column is null). I'm struggling with putting this into the Query formula.

This formula works for all but the IEP query it's in cell A3 of 'Dataset' tab:

=QUERY({November!A5:X;February!A5:X;June!A5:X}, "where Col1= '" & A1 &"' " & if(H1="All",, "and Col8 = '" & H1 &"' ") & if(E1="",, "and Col5 = '" & E1 &"' ") & "and Col6 is not null order by Col9")

I've tried several other combinations (see bottom of 'Dataset' tab for what I've tried); it's a bit out of my scope, so any help would be appreciated!

Here is the sheet

Answer 1

try:

=QUERY({November!A5:X; February!A5:X; June!A5:X}, 
 "where Col1 = '"&A1&"'"&
 IF(H1="All",,"and Col8 = '"&H1&"'")&
 IFS(E1="",, 
     E1="--",, 
     E1="None",     "and Col5 is null", 
     E1="All IEPs", "and Col5 is not null", 
     E1<>"",        "and Col5 = '"&E1&"'")&"
    and Col6 is not null 
 order by Col9", 0)