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
sql = "SELECT * FROM LiftPasses WHERE "&Session("QC_GroupType")&"= True"
or, since comparing to True is really unnecessary,
sql = "SELECT * FROM LiftPasses WHERE "&Session("QC_GroupType")
But in any case, you maybe should DEBUG to find out if the session value is what you think it is.
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>"