MIMIC VLOOKUP USING VBA

[accessjack]

I am trying to mimic the vlookup functionality of excel in access where there isn't an exact match.

tblStore has 4 fields (sample data)
Store Year Week Manager
A 2007 01 Jack
A 2007 05 Cindy
B 2007 01 Tom
C 2007 26 Bill

The way to read this data is Jack is the manager of store A from 2007 week 1 through 2007 week 4. (Even though Store A Year 2007 Week 2 thru Week 4 is not in the table, it is implied that Jack is the manager). Cindy took over as manager of store A in 2007 week 5 thru present. Tom is the current manager of store B (From 2007 Week 1 through today). The table, of course, has hundreds of stores and management changes.

Using a form [frmSelectTimeframe] with 2 combo boxes: cboYear & cboWeek, a user enters a year & week, then can press a button which runs a query called qryManager.

qryManager source is tblStore. I want the query to return a list by store with the manager as of the year & week in the combo boxes. The current query will only return results if there is an exact match; but if the user picks Year 2007 and Week 03, I want the query to return the following results:

Store Manager
A Jack
B Tom
C Null (store had no manager in 2007 week 03)

Any suggestions. Dlookup doesn't seem to work since there isn't an exact match, and I tried Elookup but couldn't figure out how to use it. Is there a way to write some VBA SQL script to only return 1 record per store.

Thanks,
AccessJack

 

[PHV]

A starting point (typed, untestested, SQL code):

SELECT A.Store, A.Year, A.Week, A.Manager
FROM tblStore AS A
WHERE 100*A.Year+A.Week = (SELECT Max(100*B.Year+B.Week) FROM tblStore AS B WHERE B.Store=A.Store AND 100*B.Year+B.Week<=200703)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[accessjack]

This worked like a champ. It was super fast & efficent. I simply placed this code directly in the query SQL view & I replaced the hardcoded year week in the code below with the two combo boxes (Year Week) concatenated together, so now it is fully automated.

Thanks.
Jack