Thread: Combining statements View Single Post
 12-17-2012, 02:46 AM PM User | #3 Old Pedant Supreme Master coder!     Join Date: Feb 2009 Posts: 24,949 Thanks: 75 Thanked 4,307 Times in 4,274 Posts Then the other way: 3 records per query. Code: ```marksSQL = "SELECT criteria_level, COUNT(*) AS criNo " _ & " FROM z_"&strSchool&"_marks, z_"&strSchool&"_criteria " _ & " WHERE marks_criteria_id = criteria_id " _ & " AND marks_username = '"&userRS("user_username")&"' " _ & " AND critieria_section_no = "&strSectionRS _ & " GROUP BY criteria_level ORDER BY criteria_level Set RS = conn.Execute( marksSql ) passNo = RS("criNo") RS.MoveNext meritNo = RS("criNo") RS.MoveNext distnctNo = RS("criNo") RS.Close marksSQL = "SELECT criteria_level, COUNT(*) AS criNo " _ & " FROM z_"&strSchool&"_criteria " _ & " WHERE critieria_section_no = "&strSectionRS _ & " GROUP BY criteria_level ORDER BY criteria_level Set RS = conn.Execute( marksSql ) totalPassNo = RS("criNo") RS.MoveNext totalMeritNo = RS("critNo") RS.MoveNext totalDistnctNo = RS("criNo") RS.Close``` Ugh...just noticed. You even have Code: ` & " WHERE critieria_section_no = "&strSectionRS _` and again, strSectionRS is clearly a number. So why the "str" prefix? If you are going to insist on using ugly Hungarian Notation, you should at least use it correctly: If a variable holds a number, it should never have a str prefix. __________________ An optimist sees the glass as half full. A pessimist sees the glass as half empty. A realist drinks it no matter how much there is.