Google sheet query has no output

Question

Very strange, a query function I have used for years with no issue is not working on my latest google sheet.

It keeps saying no output even though I know it should have an output.

This is the query

=(Query(Results!A2:E,"Select A,C,B,D,E Where A >= date '"&TEXT(B10,"yyyy-MM-dd")&"' and A <= date '"&TEXT(B10,"yyyy-MM-dd")&"' "&if(len(J6)," and C='"&J6&"' ",)&" Order by A Desc",0))

You can view the sheet at:

https://docs.google.com/spreadsheets/d/1vobnovcVLpMM3lbbDXPekNAkoj_zhlG5Kd5ksQg5rVg/edit?usp=sharing

Your help is greatly appreciated

user_15019380 · Answer

Transform Column A in dates by

=arrayformula(iferror(int(A2:A*1),date("20"&mid(A2:A,7,2),mid(A2:A,1,2),mid(A2:A,4,2))*1))

user_5632629 · Answer

try:

=ARRAYFORMULA(QUERY({TO_DATE(1*REGEXREPLACE(TO_TEXT(Results!A2:A), 
 "(\d+)/(\d+)/(\d+)(?:.*)?", "$2/$1/$3")), Results!A2:F},
 "select Col1,Col4,Col3,Col5,Col6 
  where Col1 >= date '"&TEXT(D4, "yyyy-MM-dd")&"' 
    and Col1 <= date '"&TEXT(G4, "yyyy-MM-dd")&"'"&IF(LEN(J6)," 
    and Col4='"&J6&"' ",)&" 
  order by Col1 desc", 0))

Google sheet query has no output

2 Answers