Lookup function in Access

[roseberryv]

Is there a way in Access to duplicate the functionality of Excel's
lookup function? Our company has fiscal months that don't necessarily
match the calendar months. I'd like to create a table listing the
fiscal month number, beginning date, and ending date. I would then use
this table to tell me what fiscal month a specific date falls in. For
instance, if fiscal month #1 includes December 27th through January
25th, then for December 31st I would want the value of 1 returned for
the month number. I can do this in Excel, using the lookup function.
I suppose in Access I could loop through the months table and get the
answer through VBA, but it just seems that there must be some simpler
way to do this.

 

[Arvin Meyer [MVP]]

Is there a way in Access to duplicate the functionality of Excel's
lookup function? Our company has fiscal months that don't necessarily
match the calendar months. I'd like to create a table listing the
fiscal month number, beginning date, and ending date. I would then use
this table to tell me what fiscal month a specific date falls in. For
instance, if fiscal month #1 includes December 27th through January
25th, then for December 31st I would want the value of 1 returned for
the month number. I can do this in Excel, using the lookup function.
I suppose in Access I could loop through the months table and get the
answer through VBA, but it just seems that there must be some simpler
way to do this.

 

[Arvin Meyer [MVP]]

Looks like I pushed send on the other one.

In Access, you can use DLookup to find a value. This is similar to Excel's
function. You can also build a recordset, based upon your FiscalMonth table
and get values that way. That is much faster.

 

[roseberryv]

My problem is that I won't have an exact match, which I believe is what
the DLookup function finds. If I'm looking for what fiscal month
December 31st falls in, I need Access to return the value 1 if fiscal
month one is defined as having a beginning date of December 27th and an
ending date of January 25th, for example.