QUERY Syntax with multiple cell references

Question

First time posting. I'm having difficulty with the syntax for a query I'm making in Google Sheets, the function is as follows:

=MAX(QUERY({'Fall Scoresheet'!$B$9:$V$2009;'Winter Scoresheet'!$B$9:$V$2009;'Spring Scoresheet'!$B$9:$V$2009},"Select Col10 where Col4 contains '"&$C42&"' and Col2 >= '"&Instructions!$O$21&"' and Col2 <= '"&Instructions!$O$22&"'",0))

Its returns a #N/A error stating that the "Query completed with an empty output".

I have tested and hard coding the references works; example below:

=MAX(QUERY({'Fall Scoresheet'!$B$9:$V$2009;'Winter Scoresheet'!$B$9:$V$2009;'Spring Scoresheet'!$B$9:$V$2009},"Select Col10 where Col4 contains '"&$C42&"' and Col2 >= 3 and Col2 <= 5",0))

I expect it to return the maximum value from multiple sheets based on the conditions in the function. Am I missing something simple? Any help appreciated.

Answer 1

Try skipping the apostrophe ' for the parts thats handling the numbers and see what happens. So this '"&Instructions!$O$21&"' becomes "&Instructions!$O$21&". Same goes for '"&Instructions!$O$22&"'as well. the string part '"&$C42&"' remains as is...

Answer 2

Because it's a number, you change the following sentence: and Col2 >= '"&Instructions!$O$21&"' and Col2 <= '"&Instructions!$O$22&"' to and Col2 >= "&Instructions!$O$21&" and Col2 <= "&Instructions!$O$22&"' (Note the removal of commas)