Order By Clause Conflicts With Distinct In Access?

Please help me with this as I have been unable to get past this problem When trying to execute this statement: SELECT distinct grade FROM tblStudents ORDER BY Val([grade]),grade;

Solution 1:

You cannot order by a column thats not listed in a select distinct statement; if you want grade coerced to an integer;

SELECT DISTINCT Val([grade])
FROM tblStudents
ORDER BY Val([grade]);

Solution 2:

Using the DISTINCT keyword has the same effect as grouping by all columns in the SELECT clause:

SELECT grade
  FROM tblStudents
 GROUPBY grade
 ORDERBY VAL(grade), grade;

Note I had to remove rows where grade IS NULL, otherwise I got an error, "Data type mismatch in criteria expression."

Solution 3:

Using this:

SELECT DISTINCT Val([grade])
FROM tblStudents
ORDER BY Val([grade]);

Non-numeric records shows as 0.

But the below shows both numeric and non-numeric records:

SELECT grade
  FROM tblStudents
 GROUPBY grade
 ORDERBY VAL(grade), grade;