2

I'm trying to create two cells that will update my query: A >= date"A1" and A <= date "B1".

My goal is to create a search page for the data, where the end user could insert a date or the =today() function and it would update the query. I am currently getting a formula parse error from:

=QUERY(Data!6:1397,"SELECT A,B,C,D,E,F,G,H,I,J,K,L WHERE A >= date"A1"and
 A <= date"B1"")
3

Please try:

=Query(Data!A6:L1397,"Select A,B,C,D,E,F,G,H,I,J,K,L where A>=date
 '"&TEXT(A1,"yyyy-mm-dd")&"' and A <= date '"&TEXT(B1,"yyyy-mm-dd")&"'")  

Note Column references for the source data set and that the likes of date '"&TEXT(A1,"yyyy-mm-dd")&"' are to accommodate the possible use of =today().

May be worth mention that the above would be shorter as:

=Query(Data!A6:L1397,"Select * where A>=date '"&TEXT(A1,"yyyy-mm-dd")&"' and 
A <= date '"&TEXT(B1,"yyyy-mm-dd")&"'") 
  • can you explain a bit this part: '"&TEXT(A1,"yyyy-mm-dd")&"' . What are the & for? and the TEXT? – elunicotomas Feb 19 '15 at 18:56
  • thanks, and why doesn't work If I just write: where A>=date 'A1' and A <= date 'B1'") . assuming that the user will always enter dates in A1 and B1 – elunicotomas Feb 19 '15 at 19:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.