How to fix "Query returns and empty value" error in google spread sheet

Question

I have the following code. I've read many threads on how to solve query returning empty value but none of them worked in my case. I've also shared a workaround Google spreadsheet. What I'm going to do is that search first tab by query and if it matched, show a green tick, otherwise show a red cross. The code below works for green tick but in case of red cross, it shows an error.

How the code below is expected to operate: It makes a query on tab1 and if student number is in column C (with some defined conditions) OR is in column D (without any condition), it shows a green tick, otherwise, it must insert a red cross.

if(OR(QUERY(LiveAttendanceForm!$A:$C, 
"select C
where A >= timestamp '"&TEXT(D$1, "yyyy-MM-dd HH:mm:ss")&"' 
and A <= timestamp '"&TEXT(D$2, "yyyy-MM-dd HH:mm:ss")&"' 
and C = "&$C5, 0)=$C5,Query(LiveAttendanceForm!$A:$D,"select D where D = "&$C5,0)=$C5),"✅","❌")

I also tried using iferror function before the query, but it shows all fields as true and makes all cells green tick. I'll be very grateful if someone can help me fix this annoying issue! The share Google sheet's link is below:

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

player0 · Accepted Answer · 2019-09-12 13:29:49Z

1

you will need to wrap your queries into IFERROR:

=IF(OR(IFERROR(QUERY(LiveAttendanceForm!$A:$C, 
 "select C
  where A >= timestamp '"&TEXT(D$1, "yyyy-MM-dd HH:mm:ss")&"' 
    and A <= timestamp '"&TEXT(D$2, "yyyy-MM-dd HH:mm:ss")&"' 
    and C = "&$C4, 0))=$C4,
       IFERROR(QUERY(LiveAttendanceForm!$A:$D,
 "select D 
  where D = "&$C4, 0))=$C4), "✅", "❌")

answered Sep 12, 2019 at 13:29

player0

131k14 gold badges92 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to fix "Query returns and empty value" error in google spread sheet

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related