Access Vlookup (or Dlookup) between two dates

[BigNate]

Hello Everyone,

I have a table called ListOfGroupNumbers. This table has three columns-- StartDate, EndDate, and GroupNumber. I want to use this table as a reference table to return the group number that an entry date belongs to (falls within the date range). It looks like this:

StartDate	EndDate	GroupNumber
1/7/2013	3/31/2013	Group 1
4/1/2013	5/31/2013	Group 2
6/1/2013	6/30/2013	Group 3

<tbody>
</tbody>

I have another table called "ExcelData". This table has a field called EntryDate. I want to use the EntryDate as a lookup value, and figure out which Group number that belongs to (which date range that entry date falls within), based on the table "ListOfGroupNumbers" (shown above). For example, I want to write an expression in a query so that if I have an entry date of 4/7/2013, it will return "Group 2"

It's tough because I don't know if I can get a Dlookup to work due to the fact I'm dealing with a date range rather than just a single date.

Thank you in advance for your time and effort.

 

[AOB]

=DLookup("[GroupNumber]","
[ListOfGroupNumbers]","[StartDate] <= " & [EntryDate] & " And [EndDate] >= " & [EntryDate])

...is broadly how you would do it with a DLookup function

Are you doing this through VBA? If so you might need to assign your EntryDate to a date variable and pass that to the function (remember to format the date as mm/dd/yyyy and bookend it with "#")

 

[Micron]

When getting/comparing data from more than one table, there should be a realtionship between at least each pair of them, otherwise you have to resort to hacks which don't always work reliably. DLookup can only return ONE value from any field, so if there is more than one that falls between your two dates, don't expect it to work unless you don't care which record is returned.

4/7/2013, it will return "Group 2"

I don't see why your query would return this for 04/07/2013, but I do see why if you said 04/01/2013, so that's confusing.
If you meant 04/01, then I sense you're saying the dates in your Excel table are related to the dates in ListOfGroupNumbers. If that's correct (i.e. any date in the latter would return the correct groupNumber using the same date from ListOfGroupNumbers, then just join the two tables by date in a query.

NOTE: if your date fields contain a time component (assuming they are correctly set to the date/time data type), you have other issues that will prevent this from working. Your BETWEEN statement requires special handling for date fields that contain time with respect to the end date. In those cases, the end of the end date is at the zero hour and you will get nothing with that days date if it is after (say 1:00 AM).

 

[BigNate]

Thank you both for your knowledge. I'm going to try a few things and see what happens.