title: Issues formatting numbers in Access from SQL Server query
body: <p>I have a production database that uses a SQL server back end with tables linked to an Access front end. For performance reasons, I am trying to write most of my report queries as SQL views and simply passing parameters in from Access. </p>
  
  <p>The problem I'm running into though is that I don't seem to be allowed to format fields that were calculated in the SQL view.</p>
  
  <p>For example, I have a table:</p>
  
  <pre><code>CaseLot    Drums    GrossWeight    %H2O
     01        10       10,301       5.21
  </code></pre>
  
  <p>Drums, GrossWeight, and Drums are all decimal data types but each have varying scale.</p>
  
  <p>In my view I include the values from the above table, but also the following calculated fields:</p>
  
  <pre><code>[GrossWeight] - 19.6*[Drums] AS NetWeight
  ([GrossWeight] -19.6*[Drums])*(100-[%H2O])/100 AS DryWeight
  </code></pre>
  
  <p>When I pull these calculated fields through to Access, I have no issue formatting the NetWeight field, but it won't let me for the DryWeight field. It is really frustrating, because I would like to display the DryWeight field to only two decimal places and it won't do it.</p>
  
  <p>I have also tried applying the Round() function in Access, but it seems to give me a "circular reference" error when I include a scale to round to. Round(DryWeight) works to round it to 0 decimal places, but Round(DryWeight,2) give me an error.</p>
  
  <p>What is happening here?</p>