I am trying to make a multi client selector that can be paired with a date range. I am not getting an error and the query seems to be pulling normally but no data shows up where I know there should be.
Query Below:
=query('BH Job Data'!$A:$G, "select * where C>= date '"&text(A8,"yyyy-mm-dd")&"' AND C<= date '"&text(A10,"yyyy-mm-dd")&"' AND F = '"&$A$3&"' AND F = '"&$A$4&"' AND F = '"&$A$5&"' AND F = '"&$A$6&"'",1)
I have also tried this:
=query('BH Job Data'!$A:$G, "select * where C>= date '"&text(A8,"yyyy-mm-dd")&"' AND C<= date '"&text(A10,"yyyy-mm-dd")&"' AND F = '"&$A$3&"' OR F = '"&$A$4&"' OR F = '"&$A$5&"' OR F = '"&$A$6&"'",1)
The issue with the second string is it works fine and pull just the list of 4 clients but it does not keep the query within the intended date range.
I tried both queries above and still not getting any error. The first string pulls no results and the second string pull results from correct clients but not inside the correct date range.
CodePudding user response：
You need some brackets to specify the order of operations, try this:
=query('BH Job Data'!$A:$G, "select * where C>= date '"&text(A8,"yyyy-mm-dd")&"' AND C<= date '"&text(A10,"yyyy-mm-dd")&"' AND (F = '"&$A$3&"' OR F = '"&$A$4&"' OR F = '"&$A$5&"' OR F = '"&$A$6&"')",1)
You also don't need the absolute references since this formula is not meant to be dragged.
=query('BH Job Data'!A:G, "select * where C>= date '"&text(A8,"yyyy-mm-dd")&"' AND C<= date '"&text(A10,"yyyy-mm-dd")&"' AND (F = '"&A3&"' OR F = '"&A4&"' OR F = '"&A5&"' OR F = '"&A6&"')",1)
(You could also omit select *, since it's assumed)
But if you're just filtering the data, I recommend using FILTER.
={'BH Job Data'!A1:G1;FILTER('BH Job Data'!A:G,ISBETWEEN('BH Job Data'!C:C,A8,A10),COUNTIF(A3:A6,'BH Job Data!'F:F))}