<div>
Here is what I have so far. &nbsp;But when I try to run the query I get an invalid bracketing error:<br />
<br />

<pre><code class="code-1 nolinks" id="code-20-41503304-1"><span>SELECT tblProjectPurchaseOrders.ProjectID, tblProjectPurchaseOrders.POnumber, tblProjectPurchaseOrders.Code, tblProjectPurchaseOrders.POValue, tblProjectPurchaseOrders.Distribution, [POValue]-[Distribution] AS Result, Min(tblProjectPurchaseOrders.Start) AS MinOfStart, Max(tblProjectPurchaseOrders.End) AS MaxOfEnd, Round(([End]-[Start])/7,0)+1 AS Duration, tblProjectPurchaseOrders.[Split?], ([POvalue]-[Distribution])/Nz(Round(([End]-[Start])/7,0)+1,0) AS Calculation</span>
<span>FROM tblProjectPurchaseOrders INNER JOIN tblWeekNumbers ON (tblProjectPurchaseOrders.End &lt;= [tblWeekNumbers.End]) AND ([tblProjectPurchaseOrders.Start] &gt;= [tblWeekNumbers.Start])</span>
<span>GROUP BY tblProjectPurchaseOrders.ProjectID, tblProjectPurchaseOrders.POnumber, tblProjectPurchaseOrders.Code, tblProjectPurchaseOrders.POValue, tblProjectPurchaseOrders.Distribution, [POValue]-[Distribution], Round(([tblProjectPurchaseOrders.End]-[Start])/7,0)+1, tblProjectPurchaseOrders.[Split?], ([POvalue]-[Distribution])/Nz(Round(([tblProjectPurchaseOrders.End]-[Start])/7,0)+1,0), (Int(DateDiff(&quot;d&quot;,[End],[tblProjectPurchaseOrders.Start])/7)*-1)+1, ([POvalue]-[Distribution])/((Int(DateDiff(&quot;d&quot;,[tblProjectPurchaseOrders.End],[Start])/7)*-1)+1)</span>
<span>HAVING (((tblProjectPurchaseOrders.POValue)&gt;0))</span>
<span>ORDER BY tblProjectPurchaseOrders.POnumber;</span>
</code></pre>
</div>


<div>
Is there a way to do this with an expression field in the query? &nbsp;Something like this (which of course is &quot;syntaxly&quot; incorrect?<br />
<br />
Week: = DLookup(&quot;[WeekN]&quot;, &quot;tblWeekNumbers&quot;, &quot;[Start] = Between [Start] and [End])
</div>


<div>
Yes, you can use a where clause rather than a join but then you would be doing a cross join which produces a Cartesian Product. &nbsp;All the rows of tblA joined to every row of tblB &nbsp;and then the resultset is whittled down by the where clause. &nbsp;This query would be not updateable and depending on the row count could take substantially longer. &nbsp;So, 1000 rows in tblA and 365 rows in tblB = 365,000 rows in the result set to which the where clause would be applied.<br />
<br />
I would not under any condition use a domain function as in your example. &nbsp;The domain function forces Access to run a separate query for every row of tblA and it gives the query optimizer no way to optimize. &nbsp;So, 1567 rows in tblA would require running 1567 queries to do the lookup.<br />
<br />
WHERE vs HAVING. &nbsp;Where is applied BEFORE any aggregation so it reduces the rows that go into the query whereas HAVING is applied AFTER the aggregation and so reduces the rows that come out of the query. &nbsp;HAVING is only used on aggregated data. &nbsp;It doesn't make sense to use it on non-aggregated data so it is typically used with counts or averages and the like. &nbsp;In your case, your query should be using a WHERE and not a HAVING.
</div>


<div>
It would look something like:<br />
<br />
SELECT T.*, W.WeekN<br />
FROM yourTable as T<br />
LEFT JOIN WeekNumbers as W<br />
ON T.Start &gt;= W.Start AND T.Start &lt;&nbsp;DateAdd(&quot;d&quot;, 1, W.End)<br />
<br />
Not this is a non-equi JOIN, so it will not show up in the query grid and if you attempt to view the query grid, it may break the join. &nbsp;At the very least, it will display a message that indicates it cannot display the join criteria.
</div>


<div>
This was my first suggestion (although I see now that there was a type). &nbsp;Steve asked for a different method and I was telling him why this was best.
</div>


<div>
You need no join at all, and you can easily build a query using the GUI designer using this skeleton:
<pre><code class="code-1 nolinks" id="code-20-41504131-1"><span>Select </span>
<span>    YourQuery.*,</span>
<span>    tblWeekNumbers.WeekN</span>
<span>From</span>
<span>    YourQuery,</span>
<span>    tblWeekNumbers</span>
<span>Where</span>
<span>    YourQuery.Start Between tblWeekNumbers.Start And tblWeekNumbers.End</span>
</code></pre>
Of course, you may be able to integrate this in YourQuery so you don't need an additional query.<br />
<br />
/Gustav
</div>


<div>
Sorry for the delay responding to the suggestions. &nbsp;I'm going to try to get back to this tomorrow.
</div>


<div>
<div class="content wysiwyg-content">
I have a query that has a field named &quot;Start&quot;. &nbsp;I also have a separate table named tblWeekNumbers. &nbsp;This table looks like this:<br />
<br />
<a href="https://filedb.experts-exchange.com/incoming/2016/03_w11/1086027/WeekNumbers.bmp" target="_blank" title="WeekNumbers (383 KB)"><img alt="WeekNumbers" class="file-block lazyload" style="max-width: 331px;" data-src="https://filedb.experts-exchange.com/incoming/2016/03_w11/1086027/WeekNumbers.bmp" /></a><br />
Note that week 1 is from 8/16/2015 through 8/22/2015, Week 2 is from 8/23/2015 through 8/29/2015, etc. &nbsp;The dates and the PK number in this table will never change.<br />
<br />
Now in the query I need to add a field named &quot;WeeK&quot; &nbsp;If the date in the field &quot;Start&quot; in the query is between 8/16/2015 and 8/22/2015 then I want the new field in the query named &quot;Week&quot; to show &quot;1&quot;. &nbsp;If the date in the field &quot;Start&quot; in the query is between 8/23/2015 and 8/29/2015 then I want the new field in the query named &quot;Week&quot; to show &quot;2&quot;, etc.<br />
<br />
How can I add this field and value to the query?
</div>
</div>


WeekNumbers.bmp

I have a query that has a field named "Start".  I also have a separate table named tblWeekNumbers.  This table looks like this:



Note that week 1 is from 8/16/2015 through 8/22/2015, Week 2 is from 8/23/2015 through 8/29/2015, etc.  The dates and the PK number in this table will never change.

Now in the query I need to add a field named "WeeK"  If the date in the field "Start" in the query is between 8/16/2015 and 8/22/2015 then I want the new field in the query named "Week" to show "1".  If the date in the field "Start" in the query is between 8/23/2015 and 8/29/2015 then I want the new field in the query named "Week" to show "2", etc.

How can I add this field and value to the query?

Query Question

Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.