Importrange Query importing correct OR statements, but not refining by date

Question

I have a formula pulling relevant columns from a different document. It is working well except for the last part regarding the dates. It is seemingly ignoring that part of the formula and returning all dates.

=query(importrange("https://docs.google.com/spreadsheets/d/1kwhv2eGCRPbNTg-gVMPGZ-Yx9ScRCiqlkIwxTfaEwcY/edit#gid=1133073756", "TRANSACTIONS!A6:P"), "select Col2,Col3,Col7,Col14,Col15 where Col3='FEE' OR Col3='WITHDRAWL' OR Col3='TRANSFER' OR Col3='CREDIT CARD' and Col2 >= date '"&to_text("2024-04-01")&"' AND Col2 < date '"&to_text("2024-05-01")&"'")

Answer 1

Verify that all the values of TRANSACTIONS!B6:B are dates; otherwise, QUERY might not set this column data type as date, and in such case, it will take the values that don't match the assigned data type as null.

Note: There is no need to use to_text in the formula, as the parameters of all instances are text values.

Reference

• QUERY function
• TO_TEXT function

Answer 2

TL;DR

Use the following updated formula:

=QUERY(IMPORTRANGE(R5, "TRANSACTIONS!$A$6:$P1000"), 
   "SELECT Col2, Col3, Col7, Col14, Col15
    WHERE (Col3='FEE' OR Col3='WITHDRAWL'  OR
    Col3='TRANSFER' OR Col3='CREDIT CARD') AND 
    Col2 >= date '2024-04-01'AND Col2 < date '2024-05-01'")

IMPORTRANGE  QUERY

Condition Grouping

1. The conditions that validate that your date column is within a range are never checked if Col3='FEE', or Col3='WITHDRAWL' or Col3='TRANSFER'. Only Col3='CREDIT CARD' is bound to the date conditions by the AND operator.
2. You can order the conditions to achieve your desired outcome, and also leverage groups using parenteses:

   A OR B AND date = A OR (B AND date)
   
   A AND date OR B AND date = (A OR B ) AND date
   

   Usually using parentheses uses less code and either of the following approaches would work for your formula after replacing url with the address of your spreadsheet:
   1. WITH parentheses

      =QUERY(IMPORTRANGE(url, "TRANSACTIONS!$A$6:$P1000"), 
         "SELECT Col2, Col3, Col7, Col14, Col15
          WHERE (Col3='FEE' OR Col3='WITHDRAWL'  OR
          Col3='TRANSFER' OR Col3='CREDIT CARD') AND 
          Col2 >= date '2024-04-01'AND Col2 < date '2024-05-01'")
      

      
   2. WITHOUT parentheses

      =QUERY(IMPORTRANGE(R5, "TRANSACTIONS!$A$6:$P1000"), 
         "SELECT Col2, Col3, Col7, Col14, Col15
          WHERE Col3='FEE' AND Col2 >= date '2024-04-01'AND Col2 < date '2024-05-01'
          OR Col3='WITHDRAWL' AND Col2 >= date '2024-04-01'AND Col2 < date '2024-05-01'
          OR Col3='TRANSFER' AND Col2 >= date '2024-04-01'AND Col2 < date '2024-05-01'
          OR Col3='CREDIT CARD' AND Col2 >= date '2024-04-01'AND Col2 < date '2024-05-01'")
      

      

To TEXT or not TO_TEXT

As @Rubén noted in his answer, there's no need to include TO_TEXT.

1. TO_TEXT converts a number to a text string. Including it in the formula does not make any difference except to extend the formula's length.
2. I suspect you meant to include the similarly named TEXT function that converts a number to a text string, while also applying a number format. Typically used in QUERY as follows:

   TEXT(datevalue,  "yyyy-mm-dd")
   

   This is necessary when supplying QUERY a value/number, or referencing a cell that contains a date-formatted number because regardless of the displayed format in your sheet, Sheets stores dates internally as numbers equivalent to the days that have elapsed since Dec 30, 1899, but QUERY requires dates be supplied as text/strings formatted as "yyyy-mm-dd". This does not apply to you as you are supplying properly formatted text strings. Here's an example date's formats vs. their internally stored values:

   Formated      Stored
   ------------+-------
   1899-12-31         1
   2024-05-09     45421
   May 7, 2024    45423
   5/9/2024       45425