title: Why can't I change format for numeric field in MS Access report?
body: <p>I have performance data (95%, 100%, 120%, etc.) that needs formatted as a percent so I can sort and conditionally format.  Upon opening the properties window of my MS Access report, it will let me change the format (General Number, Currency, Percent, etc.) for values extracted directly from a table, such as <code>employee_id</code> or <code>shift</code>.  But others, such as <code>[6 wks ago]</code>, <code>[5 wks ago]</code>, and on down do not allow me to do this.  Is this because the field is the result of an <code>IIF()</code> statement?  The following query is my original query:</p>
  
  <pre><code>SELECT 
  D2s_roster_tbl.employee_id AS ID,
  D2s_roster_tbl.employee_name AS Name, 
  D2s_roster_tbl.position_desc AS [Position],
  D2s_roster_tbl.adj_hire_dt AS [Hire DT], 
  D2s_roster_tbl.shift_nbr AS Shift, 
  D2s_roster_tbl.supervisor,
  IIf([D2s_roster_tbl].[Position_desc] Like "*loader*",[trndLoaderPerformanceTbl_Crosstab].[11/09],[trndPerformanceQry_crosstab].[11/09]) AS [6 wks ago], 
  IIf([D2s_roster_tbl].[Position_desc] Like "*loader*",[trndLoaderPerformanceTbl_Crosstab].[11/16],[trndPerformanceQry_crosstab].[11/16]) AS [5 wks ago], 
  IIf([D2s_roster_tbl].[Position_desc] Like "*loader*",[trndLoaderPerformanceTbl_Crosstab].[11/23],[trndPerformanceQry_crosstab].[11/23]) AS [4 wks ago], 
  IIf([D2s_roster_tbl].[Position_desc] Like "*loader*",[trndLoaderPerformanceTbl_Crosstab].[11/30],[trndPerformanceQry_crosstab].[11/30]) AS [3 wks ago], 
  IIf([D2s_roster_tbl].[Position_desc] Like "*loader*",[trndLoaderPerformanceTbl_Crosstab].[12/07],[trndPerformanceQry_crosstab].[12/07]) AS [2 wks ago], 
  IIf([D2s_roster_tbl].[Position_desc] Like "*loader*",[trndLoaderPerformanceTbl_Crosstab].[12/14],[trndPerformanceQry_crosstab].[12/14]) AS [Last wk], 
  
  FROM 
  ((D2s_roster_tbl 
  LEFT JOIN trndLoaderPerformanceTbl_Crosstab ON D2s_roster_tbl.employee_id = trndLoaderPerformanceTbl_Crosstab.employee_id) 
  LEFT JOIN trndPerformanceQry_Crosstab ON D2s_roster_tbl.employee_id = trndPerformanceQry_Crosstab.employee_id)
  LEFT JOIN trndPerfFormatPercent_Crosstab ON D2s_roster_tbl.employee_id = trndPerfFormatPercent_Crosstab.employee_id;
  </code></pre>
  
  <p>So I thought OK, it doesn't want to format the result of such a conditional statement.  So I put the series of <code>IIF()s</code> in the FROM statement and selected those values directly, as shown below:</p>
  
  <pre><code>SELECT
  D2s_roster_tbl.employee_id AS ID,
  D2s_roster_tbl.employee_name AS Name,
  D2s_roster_tbl.position_desc AS [Position],
  D2s_roster_tbl.adj_hire_dt AS [Hire DT],
  D2s_roster_tbl.shift_nbr AS Shift,
  D2s_roster_tbl.supervisor,
  j.[6 wks ago],
  j.[5 wks ago],
  j.[4 wks ago],
  j.[3 wks ago],
  j.[2 wks ago],
  j.[Last wk]
  
  FROM
  ((D2s_roster_tbl
  LEFT JOIN trndLoaderPerformanceTbl_Crosstab ON D2s_roster_tbl.employee_id = trndLoaderPerformanceTbl_Crosstab.employee_id)
  LEFT JOIN trndPerformanceQry_Crosstab ON D2s_roster_tbl.employee_id = trndPerformanceQry_Crosstab.employee_id)
  LEFT JOIN (SELECT
      d2s_roster_tbl.employee_id,
      IIf([D2s_roster_tbl].[Position_desc] Like "*loader*",[trndLoaderPerformanceTbl_Crosstab].[11/09],[trndPerformanceQry_crosstab].[11/09]) AS [6 wks ago], 
      IIf([D2s_roster_tbl].[Position_desc] Like "*loader*",[trndLoaderPerformanceTbl_Crosstab].[11/16],[trndPerformanceQry_crosstab].[11/16]) AS [5 wks ago],
      IIf([D2s_roster_tbl].[Position_desc] Like "*loader*",[trndLoaderPerformanceTbl_Crosstab].[11/23],[trndPerformanceQry_crosstab].[11/23]) AS [4 wks ago], 
      IIf([D2s_roster_tbl].[Position_desc] Like "*loader*",[trndLoaderPerformanceTbl_Crosstab].[11/30],[trndPerformanceQry_crosstab].[11/30]) AS [3 wks ago], 
      IIf([D2s_roster_tbl].[Position_desc] Like "*loader*",[trndLoaderPerformanceTbl_Crosstab].[12/07],[trndPerformanceQry_crosstab].[12/07]) AS [2 wks ago], 
      IIf([D2s_roster_tbl].[Position_desc] Like "*loader*",[trndLoaderPerformanceTbl_Crosstab].[12/14],[trndPerformanceQry_crosstab].[12/14]) AS [Last wk]
  
     FROM 
     ((D2s_roster_tbl 
     LEFT JOIN trndLoaderPerformanceTbl_Crosstab ON D2s_roster_tbl.employee_id = trndLoaderPerformanceTbl_Crosstab.employee_id) 
     LEFT JOIN trndPerformanceQry_Crosstab ON D2s_roster_tbl.employee_id = trndPerformanceQry_Crosstab.employee_id))  AS J ON d2s_roster_tbl.employee_id = j.employee_id;
  </code></pre>
  
  <p>It doesn't allow me to format the <code>[n wks ago]</code> columns from this query either.  My original performance tables have 4 columns: <code>employee_id</code>, <code>week_ending_dt</code>, <code>goal_hrs</code>, and <code>hrs_worked</code>.  This data goes for the past 6 weeks, so I created crosstab queries to give a different column for each week (we're using this to see how performance is trending week over week).  Loader performance is stored separately from others' performance, so that's why I need the <code>IIF()</code> in the query.  Is there some way to get this information that keeps the end performance values as numeric values whose format can be changed?</p>
  
  <p><a href="https://stackoverflow.com/questions/20692588/can-the-format-be-changed-for-a-calculated-field-in-the-properties-window-of-a-r">This here</a> was my original question, but my lack of specificity means this answer is not feasible.  Thank you!</p>