How to lookup date and return value from another table

[AK78]

Hi all,

I'm pretty much an Excel user and been using vlookup for dates.
Basically, i have a table ("Transaction") with payment date and another table ("Control") with accounting dates and corresponding year/month.

Objective: I need to know which accounting year or month these payment date fall under.

Example: If the payment date is 18 Dec 2013, the accounting year should read as 2013 and the accounting month should read as 12.

In excel, this is very simple using vlookup.
I tried for hours using access dlookup query and i'm still stucked ..

Please help if you can. Appreciate your time!

 

[namliam]

Well how about simply using
Month(yourdate)
Year(yourdate)

Something simular should be possible in excel as well??

 

[AK78]

I can use that for calendar year / month, no issue.
But the accounting period i have is not on calendar basis.

Not sure why, my attachment cannot be opened by myself. :banghead:
The accounting period will read something like this in dd/mm/yyyy.

FromTo AccYear AccMth
1/1/15 31/1/15 2015 1
1/2/15 27/2/15 2015 2
28/2/15 30/3/15 2015 3
31/3/15 30/4/15 2015 4

In this instance, if the payment date is 28/2/15, the accounting year and month should read as 2015 and 3 respectively.

 

[namliam]

Well in access world one does NOT or very very rarely uses DLookup
You made a valiant attempt but not quite working DLookup
Regardless this is not one of the very few times to use a DLookup.

In database world we use JOINs .... or where clauses...
To give you a good idea of what I am talking about... paste this into your query1

SELECT Transaction.*, Control.From, Control.To, Control.AccYear, Control.AccMth
FROM [Transaction], Control
WHERE (((Control.From)<=[PAYMENT_DATE]) 
  AND ((Control.To)>=[PAYMENT_DATE]));

 

[AK78]

Works like a charm !

Thank you mate for the great help.