title: How do I format numbers in and Access crosstab query to show two decimal places?
body: <p>I have an Access crosstab query that displays the following results:</p>
  
  <pre><code>| SHORE_TYPE  | Total Miles | Class 1           | Class 2          | Class 4 |
  | ONSHORE     | 31.37       | 0.337121212121212 | 12.4617424242424 | 0       |
  </code></pre>
  
  <p>I'd like it to display the following results instead. Note the 'Class' columns here show two decimal places:</p>
  
  <pre><code>| SHORE_TYPE  | Total Miles | Class 1 | Class 2 | Class 4 |
  | ONSHORE     | 31.37       | 0.34    | 12.46   | 0.00    |
  </code></pre>
  
  <p>I've been able to configure the 'Total Miles' column by changing the Format and Decimal Places properties (in the Design View) to "Fixed" and "2," respectively. However, the query column (in Design View) that determines the value in the Class column has only a Format property, which I set to "Fixed"; there is not a Decimal Places property for me to adjust.</p>
  
  <p>I have some similar crosstab queries that are showing the results in the way I desire, but I can't determine any differences between this one and those. Also, I've sometimes seen some of my queries display it the wrong way one time, then the desired way the next time. </p>
  
  <p>This makes me wonder if the problem is a bug in Access, or if there is a something implicitly defined in my code that I should explicitly define.</p>
  
  <p>Here is my SQL:</p>
  
  <pre><code>TRANSFORM IIf(IsNull(Sum([qryPartL].[MILES_OF_PHYS_LENGTH])),0,
  Sum([qryPartL].[MILES_OF_PHYS_LENGTH])) AS SumOfMILES_OF_PHYS_LENGTH
  SELECT qryPartL.SHORE_TYPE, Sum(qryPartL.MILES_OF_PHYS_LENGTH) AS [Total Miles]
  FROM qryPartL
  GROUP BY qryPartL.SHORE_TYPE
  PIVOT qryPartL.CLASS_LOC_text In ("Class 1","Class 2","Class 4");
  </code></pre>
  
  <p><strong>EDIT:</strong>
  After closing and re-opening this query, the Total Miles column is now displaying 31.3714015..., and the properties I had previously set for this column in the Design View are now blank. So, it looks like Access does not consistently save these property settings. At least not in the context in which I was using them.</p>