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!!!