title: Access '13 - Does not recognize field name Error, Crosstab to Report Issue
body: <h2>The Background</h2>
  
  <p>Each month I run a crosstab query which is then "spruced up" with an Access Report.</p>
  
  <p>CROSSTAB SQL:</p>
  
  <pre><code>TRANSFORM Sum(myTable.Field1) AS SumOfField1
  SELECT myTable.Date, Sum(myTable.Field1) AS [Daily Totals]
  FROM myTable
  GROUP BY myTable.Date
  PIVOT myTable.Field2;
  </code></pre>
  
  <p>where Field1 is a $ Amount, and Field2 (for this example) is either going to be "Option1", "Option2", or "Option3".</p>
  
  <p>TYPICAL CROSSTAB RESULTS:</p>
  
  <pre><code>Date     Option1    Option2   Option3
  -----    --------   --------  --------
  Day1      $5.00      -$2.37   
  Day2                  $3.15
  Day3                           $2.22
  </code></pre>
  
  <h2>My Issue</h2>
  
  <p>Because 99 times out of 100 I'm going to have data in each "option" in a given month I created a report that cleans up the crosstab results in a "pretty" format. This month however, my raw data didn't have any "Option3" values. As a result the crosstab column for Option3 doesn't appear on my query results. Then, since the report pulls directly from the crosstab &amp; looks for each option column by name, this causes my report break giving the error:</p>
  
  <blockquote>
    <p>The Microsoft Access database engine does not recognize '[Option3]' as
    a valid field name or expression.</p>
  </blockquote>
  
  <h2>Troubleshooting</h2>
  
  <p>I've tried a few things with respect to troubleshooting:</p>
  
  <ol>
  <li>=Nz(Sum(myTable.Field1),0) in the SQL... however this just yields 0's where NULL for the <em>existing</em> values of field2 - thus "Option3" does not appear as a column of all 0's.</li>
  <li>=Nz([Option3],0) in the report... still get the error above (field not recognized)</li>
  <li>=IIf(IsError(Option3),0,(Option3)) in the report... get the error:</li>
  </ol>
  
  <blockquote>
    <p>The expression you entered contains invalid syntax</p>
  </blockquote>
  
  <ol start="4">
  <li>=IIf(IsEmpty(Option3),0,(Option3)) in report... same as above</li>
  <li>=IIf(IsMissing(Option3),0,(Option3)) in report... same as above</li>
  </ol>
  
  <p>At this point, I'm at a loss regarding how to get my existing report to run (just displaying a column of 0's for "Option3").</p>