Assigning a number based on a range of dates

[thecrowtv]

So I have a table called StockTable with the following fields
Location, Status, Serial, Make, Model, LastDate, DotNumber

I also have a table called FCDateRange with three fields
DateStart DateEnd and DotNumber

For example
3/7/13 – 3/13/13 - 1
3/14/13 – 3/20/13 – 2

Im trying to figure how to write a query that if the lastdate from the StockTable falls during the DateStart and DateEnd fields it will assign it the number in the DotNumber field

Any help would be greatly appreciated.

 

[plog]

First, you wouldn't have a DotNumber field in StockTable--you shouldn't store calculated or redundant data. You would create a query based on StockTable and use a DLookup (http://www.techonthenet.com/access/functions/domain/dlookup.php) to pull the DotNumber value from FCDateRange.

You're query would be based on StockTable and that particular field would look like this:

LastDateDotNumber: Dlookup("[DotNumber]", "FCDateRange", "[DateStart]<=#" & [LastDate] & "# AND [DateEnd]>=#" & [LastDate] & "#")

 

[thecrowtv]

worked great thank you