Lookup values that don't match exactly

[hgus393]

Hi all,

I am trying to match dates in a master table called Buckets that contain 65 working days and a consecutive identifier for each workday, for ex wokday 1 has an identifier called D1 etc, to a working table called Workdata. The Workdata table has some dates that are not workdays. How can I match the dates to the nearest working date, basically Excels Vlookup (with the true indicator at the end)?

Cheers

Rob

 

[vbaInet]

Can't quite understand what you're after hgus. Can you show us a spreadsheet of what you're trying to do? Or some screenshots.

 

[hgus393]

Can't quite understand what you're after hgus. Can you show us a spreadsheet of what you're trying to do? Or some screenshots.

Hi,

Sorry for being unclear. All this is in Access. I have posted a sample database.
There are two tables:
MainTbl that contains dates and a text field .
Buckets contain dates and an indicator D1 to D65 that correlates with the first working day to the 65th working day. What I am trying to do is a query where I can match the MainTbl dates with the Buckets dates and deliver the indicator field + the fields in the MainTbl. If the MainTbl has a date that is not in the buckets table it should return the nearest date and indicator. Excel has the function Vlookup where you can specify that if it doesn't find an exact match it should take the closest one...this is what I would like to do in Access.

Cheers

Rob

 

[vbaInet]

See attached the query qryCloseOrNear.

 

[hgus393]

See attached the query qryCloseOrNear.

Awesome!! Thank you very very much!!

Cheers

Rob

 

[vbaInet]

You're welcome!