Date Query in Google Sheets with importrange

Question

I have two tables. Table A= Customer Feedback Table B= KPI

In Table A I have a column for a date with format mm/dd/yyyy

and in Table B I have 2 columns as from date and to date with the format.

I want a count of all rows from Table A that lies within from date and to date in Table B

I am using below query:

=COUNT(query(IMPORTRANGE("14WxbiYeiY7fX6-V0f2MM4SraphCoa6EHD03F_6NmZ3g",
 "Customer_Feedback_Report!A2:L250"),
 "select Col9 where Col8 = '"&Sheet1!A2&"' and Col4 > date 
 '"&TEXT('"&Sheet1!J2&"',"yyyy-mm-dd")&"' and Col4 <= date 
 '"&TEXT('"&Sheet1!K2&"',"yyyy-mm-dd")&"' ",1))

this query works fine if there is no date verification involved:

=Count(query(IMPORTRANGE("14WxbiYeiY7fX6-V0f2MM4SraphCoa6EHD03F_6NmZ3g",
 "Customer_Feedback_Report!A2:L250"),"select Col9 where Col8 = '"&Sheet1!A2&"' "))

but with date verification it is showing error. Can anyone help?

Answer 1

you got #ERROR! because you wrapped Sheet1!J2 and Sheet1!K2 between '"& &"' ...try:

=COUNT(QUERY(IMPORTRANGE("14WxbiYeiY7fX6-V0f2MM4SraphCoa6EHD03F_6NmZ3g",
                         "Customer_Feedback_Report!A2:L250"),
             "select Col9 where Col8 = '"&Sheet1!A2&"' and Col4 >  date 
             '"&TEXT(Sheet1!J2, "yyyy-mm-dd")&"'       and Col4 <= date 
             '"&TEXT(Sheet1!K2, "yyyy-mm-dd")&"' ", 1))