Returning empty query
I have a spreadsheet connected to a form for families to sign up for tours of a school, and I can't get two (probably 3) of my tabs to return any data, aside from the header row.
Where I first noticed the problem was on the "Future visits" tab, which is returning an empty output for this formula:
=QUERY('Date Filtering'!A1:J, "Select I,B,C,D,E,J where I > date '"&TEXT(today(),"yyyy-mm-dd")&"' order by I asc",1)
But I think the same problem is also happening on the "Past visits" and "Today's visits" tabs, too. "Past visits" should have some data, and although no tours are today, I would guess "Today's visits" is likely to have the same issue.
I've tried adjusting the formatting of the data to make sure it's seen as dates, switching to "Col9" notation instead, and removing pieces of the formula to diagnose what's wrong, but I am absolutely stuck.
Anyone see an issue that would explain why the formula is returning an empty query?
query() does not react well with mixed data-types present in a single column. Your Tour date - column_I which you are referencing within the query has both dates & text, which is resulting in this error scenario. this article here gives a detailed explanation on what this issue is & how to deal with it and all. One suggestion would be to convert the whole column to text-format and not let it be mixed-type
alternatively you could also look into filter():
=filter('Date Filtering'!B:F,'Date Filtering'!I:I=today()-1)
