Lookups in Access

[roseberryv]

Is there a way to do an 'inexact' match lookup in Access as is
possible in Excel? For instance, in Excel, you can create a table
showing the starting dates of fiscal periods (which may or may not be
the same as the calendar months). You can then do a lookup on a
particular date and if it's greater than value A but less than value
B, Excel will return value A, even though the date passed to the
function was not an exact match of value A.

I suppose you could create a table which included all the dates in each
fiscal period, but I was curious if there was an easier way to
accomplish this in Access.

 

[Allen Browne]

Yes, create the table with the starting dates.

You can then use DMax() to get the highest date value before a particular
date, or DMin() to get the lowest date after a particular one.

If you need a value from another field matching that date, DLookup() can't
do it, but ELookup() can:
http://allenbrowne.com/ser-42.html

If you need something that is more efficient to execute (but takes a bit
more work to set up), see:
Lookup in a range of values in a query
at:
http://allenbrowne.com/ser-58.html

 

[kingston via AccessMonster.com]

Have you tried to use the criteria Between... And...:

...Where [MyDate] Between [StartDate] And [EndDate]...

It's not clear how you plan on implementing this. Do you have a table of
fiscal dates and a table of dates you want to check? If you have a table of
fiscal begin dates, add a column of fiscal end dates so that you can check
dates with Between...

 

[roseberryv]

Yes, create the table with the starting dates.

You can then use DMax() to get the highest date value before a particular
date, or DMin() to get the lowest date after a particular one.

If you need a value from another field matching that date, DLookup() can't
do it, but ELookup() can:
http://allenbrowne.com/ser-42.html

If you need something that is more efficient to execute (but takes a bit
more work to set up), see:
Lookup in a range of values in a query
at:
http://allenbrowne.com/ser-58.html

I read the 'Lookup in a range of values in a query" page and was able
to work out how to construct a query that would give me what I need.
Thanks!