title: SQL query to select records where date field is certain week number
body: <p>I have no own "script syntax" for my application. It is designed for creating simple reports from a Microsoft Access Database using JET. Here is a simple example:</p>
  
  <pre><code>REQUIRES "Min number of away talks:" AS $iMinNumAwayTalks
  LOOP FROM "Home Talks" NODATERANGE WHERE "Last Given" ISMOREOREQUAL "$Today"  SORTBY "Last Given"
      SHORTDATE_FIELD "Last Given"
      TAB =5&gt;
      TEXT "Week: "
      VARIABLE_FIELD "Last Given" "$iWeekNo#CUSTOMDATE[%W]"
      TEXT "$iWeekNo"
      $iAwayTalkCount = 0
      LOOP FROM "Away Talks" NODATERANGE WHERE "Talk Date" ISMOREOREQUAL "$Today"  SORTBY "Talk Date"
          VARIABLE_FIELD "Talk Date" "$iAwayWeekNo#CUSTOMDATE[%W]"
          IF "$iAwayWeekNo" IS "$iWeekNo"
              $iAwayTalkCount = $iAwayTalkCount + 1
          END_IF
      END_LOOP
      TAB =10&gt;
      TEXT "# Away: "
      TEXT "$iAwayTalkCount"
      TAB =15&gt;
      TEXT "$iMinNumAwayTalks"
      TEXT " Exceed? "
      IF "$iAwayTalkCount" ISMORE "$iMinNumAwayTalks"
          TEXT "Yes"
      END_IF
      IF "$iAwayTalkCount" ISLESSOREQUAL "$iMinNumAwayTalks"
          TEXT "No"
      END_IF
      EOL
  END_LOOP
  </code></pre>
  
  <p>It is basically looking at two tables to work out how many records have the same <strong>Week Number</strong> (beginning <strong>Monday</strong>) for the specified fields.</p>
  
  <p>My question relates to this query I have used:</p>
  
  <pre><code>LOOP FROM "Away Talks" NODATERANGE WHERE "Talk Date" ISMOREOREQUAL "$Today"  SORTBY "Talk Date"
  </code></pre>
  
  <p>The downside with this query is that is is selecting all records greater than today and then I have to examine each record to see if the "Talk Date" field has the same week number.</p>
  
  <p><a href="https://i.stack.imgur.com/UAeVh.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/UAeVh.png" alt="Results"></a></p>
  
  <p>Now, my script syntax supports raw SQL loops too. So is it possible using a raw Microsoft Access JET SQL query to:</p>
  
  <p>Select all <strong>Away Talks</strong> where the <strong>Talk Date</strong> field is <strong>Week X</strong> (where Week is week starting <strong>Monday</strong>)?</p>
  
  <p>I have tried to do a manual test in Access 2016 with the criteria:</p>
  
  <pre><code>SELECT [Away Talks].[Talk Date]
  FROM [Away Talks]
  WHERE ((DatePart('ww',[Talk Date],'vbMonday')=45));
  </code></pre>
  
  <p>But I have an error:</p>
  
  <p><a href="https://i.stack.imgur.com/sL5AV.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/sL5AV.png" alt="Access Error"></a></p>
  
  <p>What am I doing wrong?</p>