title: Access 2007: Can I use Format() in query Criteria?
body: <p>It seems I'm always trying to do the impossible. Well, I have a [month/day] table setup in the following format:</p>
  
  <pre><code>Noombers | Lemmas
  -----------------
  0101     | Jan 1
  0102     | Jan 2
  ...      | ...
  1230     | Dec 30
  1231     | Dec 31
  </code></pre>
  
  <p>I have a table of [Publication Venues] with fields [Reads From] and [Reads To] that utilize this [month/day] table in that, when I enter the info, I just type in "Sep 1" and hit tab, which fills the field (in the background) with "0901". It's a text field because I want to sort alphanumerically and I also want to do an alphanumeric comparison. I thought I could do something like this in the Criteria for the query I've created for showing a list of publication venues that are currently reading:</p>
  
  <pre><code>Field:       Reads From              | Reads To
  Table:       Publication Venues      | Publication Venues
  Sort:       
  Show:    
  Criteria:    &gt;Format(Date(),"mmdd")  | &lt;Format(Date(),"mmdd")
  </code></pre>
  
  <p>But, though I am not seeing any errors, I am also not seeing the records that <em>should</em> be showing up. In fact, I'm not seeing any records.</p>
  
  <p>So, is it safe for me to assume at this point that I can't use Format() in a query's Criteria? If so, then what is another way for me to go about setting up this filter? Using the table as I've already got it, preferably, since I've already got hundreds of records entered. If I can use Format(), then what do you suppose I'm doing wrong?</p>
  
  <p>Here's the SQL:</p>
  
  <pre><code>SELECT
      [Publication Venues].*
  FROM
      [Publication Venues]
  WHERE
      ((([Publication Venues].[Still Active?])=Yes)
    AND
      (([Publication Venues].[Reads From])&lt;Format(Date(),"mmdd"))
    AND
      (([Publication Venues].[Reads To])&gt;Format(Date(),"mmdd")));
  </code></pre>