Your SQL looks perfectly reasonable to me, assuming: (1) The value in the session variable really is the name of a field in the table and (2) You really are using a TEXT field of some kind and it really does have some records where the value is 'YES'.
If this is an Access database and if the field in question is a Yes/No field, then the problem is that 'Yes' is never actually stored in such fields. Only from Access-the-program will you ever actually see a value as 'Yes'. From ADO, the field will be a boolean field, having True and False values.
NOTE: True and False are *keywords*. You do *NOT* put apostrophes around them if you use them as such!
If that is the case, just use
or, since comparing to True is really unnecessary,
sql = "SELECT * FROM LiftPasses WHERE "&Session("QC_GroupType")&"= True"
But in any case, you maybe should DEBUG to find out if the session value is what you think it is.
sql = "SELECT * FROM LiftPasses WHERE "&Session("QC_GroupType")
So no matter which variant you use, you should be doing
sql = "SELECT * FROM LiftPasses WHERE "&Session("QC_GroupType")&"= 'Yes'" ' or other variant
Response.Write "<hr>DEBUG SQL: " & sql & "<hr>"