Query Table with Multiple Currencies Based on Date Range

Question

I am trying to create a query based on a date range, that will display multiple currencies and re-label a column header. Here is the sample dataset I'm working with.

I'm able to successfully query the table using =ArrayFormula(QUERY({A4:B14,TO_TEXT(C4:C14),D4:D14},"Select *")). (without TO_TEXT, any currency that is not $USD does not display in the output)

However, if I try to select a column instead of Select * AND/OR if I add criteria such as a date range, it creates a #VALUE error.

Here is the full formula that is not working:

=QUERY(ARRAYFORMULA({A4:B,TO_TEXT(C4:C),D4:D}),"select A, B, C where D >= date '"&TEXT(A$2,"yyyy-mm-dd")&"' and D <= date '"&TEXT(B$2,"yyyy-mm-dd")&"' Label C 'Cost'")

Thanks in advance for any help!

Answer 1

use:

=QUERY(ARRAYFORMULA({A4:B,TO_TEXT(C4:C),D4:D}),
 "select Col1, Col2, Col3 
  where Col4 >= date '"&TEXT(A$2,"yyyy-mm-dd")&"' 
    and Col4 <= date '"&TEXT(B$2,"yyyy-mm-dd")&"'")

when range is constructed you need to use Col reference in your query