I am trying to use the QUERY function to organize data from a raw data set. I am trying to filter to specific recruiters as well as specific status types from said recruiter. All this is working normally.

I recently wanted to add the ability to pick a specific date range to drill down even further. This is where my QUERY breaks. My current formula is below.

I am currently NOT getting any ERROR message. It is almost as if the last piece of the formula is just being ignored! Worth noting that I am trying to use the WHERE clause to combine all these things together.

=query('BH Sub Data'!A:H,"select * where 1=1 "&if(A2="All Recruiters",""," AND LOWER(A) = LOWER('"&A2&"') ")&if(A3="All Statuses",""," AND LOWER(D) = LOWER('"&A3&"')&' and B >= date '"&text(A5,"yyyy-mm-dd")&"')&' and B <= date '"&text(A7,"yyyy-mm-dd")&"',1)"))

Dashboard

I blocked out sensitive info partially.

I have tried using different tutorials on youtube/searching the web but have not found a combination of code that seems to fix the issue.

I have also tried messing around with the single and double quotations around to see if my syntax was off. The current code above is the only code that doesn't give me an error.

CodePudding user response：

can you try this:

=query('BH Sub Data'!A:H,"Select * where 1=1 "&if(A2="All Recruiters",," AND LOWER(A) = LOWER('"&A2&"')")&if(A3="All Statuses",," AND LOWER(D) = LOWER('"&A3&"')")&" AND B>= date '"&text(A5,"yyyy-mm-dd")&"' AND B<= date '"&text(A7,"yyyy-mm-dd")&"' ")

CodePudding user response：

should be:

=QUERY('BH Sub Data'!A:H,
 "where 1=1 "&
 IF(A2="All Recruiters",," and lower(A) = '"&LOWER(A2)&"'")&
 IF(A3="All Statuses",,  " and lower(D) = '"&LOWER(A3)&"'")&
 " and B >= date '"&TEXT(A5, "e-m-d")&"'"& 
   and B <= date '"&TEXT(A7, "e-m-d")&"'", 1)