[X]My Assistant

[Questvba]

Hello UtterAccess,
This is my very first topic and I hope to respect all the rules. Excuse me for my English which is not very good.
Here is certainly the stupid question of the day for you....

I have two Tables: PUTIN and DATA.

Table 1
Name of table = PUTIN
Field 1 = PERSONS
Field 2 = DATE
 Image_2.png ( 7.11K )Number of downloads: 1


Table 2
Name of Table = DATA
Field 1 = MOIS
Field 2 = AMOUNT
 Image_3.png ( 7.69K )Number of downloads: 1


My problem: how, for each person (PERSONS) to recover the amount due (AMOUNT) according to the calculation date (DATE) in relation to the time of indexation (MOIS). I tried it with Dlookup but I can't get anywhere. Either
it's not the right solution or I use it wrong.

If a good soul could put me on the track....

In XL, I would have done:

CODE

=INDEX(DATA[AMOUNT];EQUIV([@DATE];DATA[MONTH];1))

 Image_5.png ( 15.8K )Number of downloads: 5


Excel is fine, but now I want it in Access.
This post has been edited by Questvba: Oct 30 2019, 06:53 AM

[June7]

You want Amount from record where MOIS is closest to Date but not later? DLookup alone will not do that.

DLookup("Amount","Data","MOIS=DMax('MOIS','Data','Mois<=#" & [Date] & "#)'")

Date is a reserved word and should not use reserved words as names for anything.

[ipisors12]

You can look into opening a recordset

dim rs as dao.recordset, db as dao.database
set db=currentdb
set rs = db.openrecordset("select something with ORDER BY")

do until rs.eof=true
rs.movenext
if rs.fields(field)=something
loop


...you get the idea. maybe?

[Questvba]

Here is the solution I adapted to my case.
Thank you very much for putting in orbit.

CODE

SELECT [PUTIN]![PERSONS] AS Expr1, PUTIN![MONTH_P] AS Expr2, format(DLookUp("AMOUNT","DATA","[MONTH_D] <= #" & Format([MONTH_P],"mm/dd/yyyy") & "#"),"0.00") AS Expr3
FROM PUTIN;

How do I close the toptic?
This post has been edited by Questvba: Nov 4 2019, 07:31 AM