title: SQl Use IIF to compare 2 values in same row
body: <p>I am using SQL in MS Access and need to compare 2 values in a row to determine the result of a 3rd field. 
  The "Yearmove" field does not have a value in all cases. Here is the test code I am running</p>
  
  <pre><code>SELECT z.SCHOOL, z.Year as Year,  ProgBuild.BldgNo, ProgMoveBuild.yearmove AS Yearmove,
  IIF([Year]=[Yearmove], 1, 0) AS Result
  </code></pre>
  
  <p>The results are fine in the cases where "Yearmove" is blank, and show 0 as expected. Anyplace "Yearmove" has a number the Result field reads "#ERROR", so both when the condition is met and when it is not. I have tried populating the "YEAR" field with both text (e.g. "2014") and non-text (e.g. 2014) but get the same result. All data is numeric. 
  Here is what a section of the results look like: </p>
  
  <pre><code>SCHOOL  Year    BldgNo  Yearmove    Result
  254     2014     254                   0
  256     2014     256                   0
  260     2014     260                   0
  261     2014     261                   0
  262     2014     202                   0
  301     2014     301                   0
  307     2014     307     2019        #ERROR
  313     2014     313     2019        #ERROR
  314     2014     314                   0
  321     2014     321                   0
  322     2014     322                   0
  </code></pre>