title: How to order (sort) formatted numbers in an Access Union Query (SQL)
body: <p>I've created the following Union Query in Access, but I'm having trouble ordering the "hourly" values when they are formatted the way I wish. Because the query of the Emp_Police table returns no records (due to the Like "SP*" filter), I get an error of:</p>
  
  <blockquote>
    <p>"The ORDER BY expression (Hourly) includes fields that are not selected by the query. Only those fields requested in the first query can be included in an ORDER BY expression."</p>
  </blockquote>
  
  <p>The ORDER BY expression works when the fields are not formatted but not when they are. I've also tried the ORDER BY expression as </p>
  
  <pre><code>ORDER BY Format([Hourly],"Standard") DESC 
  </code></pre>
  
  <p>but that doesn't seem to work either.</p>
  
  <pre><code>SELECT Emp_Norm.UnionCode, Emp_Norm.EMPNAME, Format([Hourly],"Standard") AS Expr1, Format([Salary],"Standard") AS Expr2
  FROM Emp_Norm
  WHERE (((Emp_Norm.UnionCode) Like "SP*"))
  UNION
  SELECT Emp_Police.UnionCode, Emp_Police.EMPNAME, Format([Hourly],"Standard") AS Expr1, Format([Salary],"Standard") AS Expr2
  FROM Emp_Police
  WHERE (((Emp_Police.UnionCode) Like "SP*"))
  ORDER BY Hourly DESC;
  </code></pre>
  
  <p>As a smaller issue, the formatted numbers are left justified as if they are text not right justified as numbers should be.</p>