Looking up value in a range

[Guest]

I have a table with three columns; [BeginDate],[EndDate],[Number].
If a given date falls within the [BeginDate] and the [EndDate] I want to
return the value in [Number].
Sound simple enough, but I can not figure out how to do it in a query.

 

[Allen Browne]

Try:
= DLookup("Number", "Table1", "#1/1/2005# Between [BeginDate] And [EndDate")

If you need to read the date from text box Text1:
= DLookup("Number", "Table1", Format([Text1], "\#mm\/dd\/yyyy\#") & "
Between [BeginDate] And [EndDate")

 

[John Spencer]

Not quite sure what you are looking for. Perhaps

SELECT BeginDate, EndDate, [Number]
FROM Table
WHERE #1/1/2005# Between BeginDate and EndDate

Faster method if there are lots of records and indexes on beginDate and EndDate

SELECT BeginDate, EndDate, [Number]
FROM Table
WHERE BeginDate <= #1/1/2005 and EndDate >= #1/1/2005#