Search code examples
if-statementgoogle-sheetsgoogle-sheets-formulagoogle-sheets-querygoogle-query-language

Dropdown query has no error, but returns no results


I'm hoping to allow others to sort through the data using some dropdowns, but they shouldn't have to use all of them if they don't need to.

My query function:

=QUERY(CATALOG!A2:I259,"SELECT * WHERE 1=1 "&IF(A2="Any",""," AND B = '"&A2&"' ")&IF(B2="ANY",""," AND C = '"&B2&"' ")&IF(C2="Any",""," AND D = '"&C2&"' ")&IF(D2="Any",""," AND E = '"&D2&"' ")&IF(E2="Any",""," AND F = '"&E2&"' ")&IF(F2="Any",""," AND G = '"&F2&"' ")&IF(G2="Any",""," AND H = '"&G2&"' "),1)

Whenever I ran this, there wasn't an error but the query didn't give any items.

I initially only tested one of the dropdowns but received nothing. I plugged in a known product into the inputs and still received nothing.

Link to copy of the spreadsheet with dataset and function https://docs.google.com/spreadsheets/d/1s3tOm_6g8n66HT9md3EAXY7XwbkpmPggYhxdF-zv5ok/edit?usp=sharing


Solution

  • Some of the search parameters seem to be numbers. In that case do not use the single quotes (as that will turn them into strings). See if this works

    =QUERY(CATALOG!A2:I259,"SELECT * WHERE 1=1 "&IF(A2="Any",""," AND B = "&A2&" ")&IF(B2="ANY",""," AND C = '"&B2&"' ")&IF(C2="Any",""," AND D = '"&C2&"' ")&IF(D2="Any",""," AND E = '"&D2&"' ")&IF(E2="Any",""," AND F = "&E2&" ")&IF(F2="Any",""," AND G = "&F2&" ")&IF(G2="Any",""," AND H = '"&G2&"' "),1)