Assign a month number based on matching a date to a range in a table 1

[BroomerEd]

I have a table for accounting months. It is structured as follows:
Start End Mth No
Oct 1, 2007 Oct 27, 2007 1
Oct 28, 2007 Nov 24, 2007 2
Nov 25, 2007 Dec 29, 2007 3
(You get the idea)

I have a record in a query that contains a start date and want to assign the Mth No that pertains to the start date from the accounting month table. For example, if the start date is Nov 30, 2007, I want the Mth No assigned to be 3. I can't get the proper month number to come up via my query where I formulate "iif(start date>=start and start date<=end, mth no, 0)", unless the start date is exactly equal to either the start or end date in the table for accounting months. Any dates in between are not recognized.

 

[lameid]

I'm not sure of your application but perhaps you want something like...

Dlookup ("[Mth No]", "Table", "Start <= #" & Date() & "#
And End >= #" & Date() & "#")

 

[PWise]

[Code Jet Sql]
Select StartDate, ....... AM.[Mth No]
from QueryName As QN
inner join [accounting months] As Am
on QN.StartDate Between AM.[Start] and AM.[end]

[/code]