Google Sheets Query function sort breaks when using drop-down filters

Question

I have a query in my Google Sheet to pull student testing data from a master list based on drop-down menu options. My goal is to sort the filtered data by Column L (Completion Date) so that I can graph student (or grade, or group) progress over time. The query works perfectly to filter data based on the drop-down selections, but it does not work if I add "order by" to the query. If I remove the "IF" statements after the query that filter the data based on drop downs, the sort works.

Here is the query as currently written:

=Query('ELA Data'!A1:AY,"Select A,B,L,E,K,J,O,Q,R,AX,T,X,Y,AA,AB,AD,AE,AG,AH,AJ,AK,AM,AN,AP,AQ WHERE A is not null"&IF(A2="All Students",""," AND A = '"&A2&"' ")&IF(B2="All Grades",""," AND E = '"&B2&"' ")&IF(C2="All SPED",""," AND K = '"&C2&"' ")&IF(D2="All ELL",""," AND J = '"&D2&"' ")&IF(E2="All Academic Years",""," AND F = '"&E2&"' ")&IF(F2="All Overall Relative Placement",""," AND S = '"&F2&"' "),1)

With the "order by":

=Query('ELA Data'!A1:AY,"Select A,B,L,E,K,J,O,Q,R,AX,T,X,Y,AA,AB,AD,AE,AG,AH,AJ,AK,AM,AN,AP,AQ WHERE A is not null order by L"&IF(A2="All Students",""," AND A = '"&A2&"' ")&IF(B2="All Grades",""," AND E = '"&B2&"' ")&IF(C2="All SPED",""," AND K = '"&C2&"' ")&IF(D2="All ELL",""," AND J = '"&D2&"' ")&IF(E2="All Academic Years",""," AND F = '"&E2&"' ")&IF(F2="All Overall Relative Placement",""," AND S = '"&F2&"' "),1)

If I don't change the drop-down menus from "All xyz," the sort works. If I select a different option, I get a #VALUE! error: "Unable to parse query string for Function QUERY parameter 2: PARSE_ERROR: Encountered " "and" "AND "" at line 1, column 102." Here is a copy of the deidentified data so you can get an idea of what I'm working with.

Any assistance would be greatly appreciated!!!

Answer 1

The order by clause needs to come after all the parts of the where clause. To make your formula easier to debug, put this formula in cell A5:

="select A, B, L, E, K, J, O, Q, R, AX, T, X, Y, AA, AB, AD, AE, AG, AH, AJ, AK, AM, AN, AP, AQ where A is not null " & if(A2 = "All Students", "", " and A = '" & A2 & "' ") & if(B2 = "All Grades", "", " and E = '" & B2 & "' ") & if(C2 = "All SPED", "", " and K = '" & C2 & "' ") & if(D2 = "All ELL", "", " and J = '" & D2 & "' ") & if(E2 = "All Academic Years", "", " and F = '" & E2 & "' ") & if(F2 = "All Overall Relative Placement", "", " and S = '" & F2 & "' ") & " order by L"

Then modify the formula in cell A9 like this:

=query('ELA Data'!A1:AY, A5, 1)

See the query language help page.