Lookups in Access

  • Thread starter Thread starter roseberryv
  • Start date Start date
R

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.
 
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
 
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...
 
Allen said:
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
Click to expand...

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!
 
Back
Top